Geek Answers Handbook

SQL query with multiple values ​​in one column

I hit my head on the table, trying to figure it out. I have a table in which work data is stored, and the reasons why the work does not end. The reasons are numerical, 01.02.03, etc. You can have two reasons for your pending work. If you select two reasons, they will be stored in the same column, separated by a comma. This is an example from the JOBID table :

Job_Number     User_Assigned     PendingInfo

1              user1             01,02

There is another table called Wait that stores what these values ​​actually represent. 01 = Not enough information, 02 = Not enough time, 03 = View wait. Example:

Pending_Num    PendingWord

01             Not Enough Info
02             Not Enough Time

What I'm trying to do is query the database to give me all the job numbers, user numbers, pendinginfo and the pending reason. I can snatch the first value, but I can’t figure out how to make the second. What are my limited skills:

select Job_number,user_assigned,SUBSTRING(pendinginfo,0,3),pendingword
from jobid,pending
where
    SUBSTRING(pendinginfo,0,3)=pending.pending_num and
    pendinginfo!='00,00' and
    pendinginfo!='NULL'

What I would like to see for this example would be:

Job_Number  User_Assigned   PendingInfo   PendingWord       PendingInfo  PendingWord

1           User1           01            Not Enough Info   02           Not Enough Time

Thanks in advance

+5
source share
6 answers

You really shouldn't store multiple items in the same column if your SQL ever wants to process them individually. The "SQL gymnastics" you have to perform in these cases are both ugly hacks and performance degradation.

, 3NF , ( , , , , ).

.

, , SQL, - :

where find ( ',' |fld| ',', ',02,' ) > 0

, SQL- (find , charindex SQLServer).

, ( ) ( , ).

, , DBA. DBA , DBA , : -).

insert/update, , .

, .

, / , select`, , .

, - . , .

+5

, , .
, , :

JOBS TABLE
jobID | userID
--------------
1     | user13
2     | user32
3     | user44
--------------

PENDING TABLE
pendingID | pendingText
---------------------------
01        | Not Enough Info
02        | Not Enough Time
---------------------------

JOB_PENDING TABLE
jobID | pendingID
-----------------
1     | 01
1     | 02
2     | 01
3     | 03
3     | 01
-----------------

JOIN .
- , , .

+5

:

Events
---------
eventId int
eventTypeIds nvarchar(50)
...

EventTypes
--------------
eventTypeId
Description
...

.

, , 2 , SQL

  • (eventTypeIds) "3,4,15,6" ViewState, .

  • , , ,

+3

(, , ), " ", ? , , " " ...

+2

, 

;WITH Numbers AS 
( 
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS N
    FROM JobId
), 
Split AS 
( 
    SELECT JOB_NUMBER, USER_ASSIGNED, SUBSTRING(PENDING_INFO, Numbers.N, CHARINDEX(',', PENDING_INFO + ',', Numbers.N) - Numbers.N) AS PENDING_NUM
    FROM JobId
    JOIN Numbers ON Numbers.N <= DATALENGTH(PENDING_INFO) + 1 
    AND SUBSTRING(',' + PENDING_INFO, Numbers.N, 1) = ','
) 
SELECT *
FROM Split JOIN Pending ON Split.PENDING_NUM = Pending.PENDING_NUM

, , PENDING_NUM s.

+1

DBA , , , .

, 10000 , 1000 . user_groups , groupID membersID. membersID : (', 10,2001,20003,333,4520,'), -, . . "%, someID,%".

If you cannot change your data ('01, 02,03 ') or similar, say you need lines containing 01, you can still use "select ... LIKE '01,%' OR '%, 01' OR '%, 01,%' ", which ensures that it will match if at the beginning, at the end or inside, avoiding a similar number (that is: 101).

0
source

More articles:


All Articles