SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query help


query help

Author
Message
etirem
etirem
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 535
IF OBJECT_ID('Tempdb..#tStudents') IS NOT NULL
DROP TABLE #tStudents

CREATE TABLE #tStudents(
[StudentID] [varchar] (6) NOT NULL ,
[StudentName] [varchar](50) NOT NULL,
[Code1] [varchar] (MAX) NULL,
[Code2] [varchar] (MAX) NULL,
[Code3] [varchar] (MAX) NULL)
GO

INSERT INTO #tStudents VALUES ('SID1','Amar','E1,E2,E3','R1,R2, ','Y1,Y2,Y3')
INSERT INTO #tStudents VALUES ('SID2','Bobi','X1,X2,X3','D1,D2,D3','K1,K2,K3')
INSERT INTO #tStudents VALUES ('SID3','Cathy','A1,A2,A3','E1,E2,E3','S1, ,S3')
GO
SELECT * FROM #tStudents

;WITH Cte AS
(
SELECT
[StudentID],
[StudentName],
CAST('<M>' + REPLACE([Code1], ',' , '</M><M>') + '</M>' AS XML) AS [Code1]
FROM #tStudents
)
Select
[StudentID],
[StudentName],
Split.a.value('.', 'VARCHAR(MAX)') AS [Code1]
FROM Cte
CROSS APPLY [Code1].nodes('/M')Split(a)
--Need to add Code2 and Code3 columns



Output Needed:
StudentID StudentName Code1 Code2 Code3
SID1 Amar E1 R1 Y1
SID1 Amar E2 R2 Y2
SID1 Amar E3 Y3
SID2 Bobi X1 D1 K1
SID2 Bobi X2 D2 K2
SID2 Bobi X3 D3 K3
SID3 Cathy A1 E1 S1
SID3 Cathy A2 E2
SID3 Cathy A3 E3 S3


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80955 Visits: 17946
How do you determine which Code1 is matched with which Code2 and which Code3 in your result set? Will all codes always have the same number of elements in?

John
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46182 Visits: 15686
If not the same number, is it the order in which they appear in the delimited list? (which, in your examples is the same, but who knows for your real data Smile)


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
etirem
etirem
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 535
yes and yes for above questions.
Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46182 Visits: 15686
etirem - Tuesday, December 12, 2017 5:13 AM
yes and yes for above questions.

It can't be both... If I have the delimited list 'E1, E3, E2', you're stating it must be supplied in the order of the list and in the order of the numeric part. E3 appears in the list prior to E2, but 2 is less than 3.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
etirem
etirem
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 535
Thom A - Tuesday, December 12, 2017 5:21 AM
etirem - Tuesday, December 12, 2017 5:13 AM
yes and yes for above questions.

It can't be both... If I have the delimited list 'E1, E3, E2', you're stating it must be supplied in the order of the list and in the order of the numeric part. E3 appears in the list prior to E2, but 2 is less than 3.

Sorry for the confusion. Only the order of the list and it will be always 3 values for Code cols.
For each StudentID, StudentName, there are 3 cols with Code1,Code2 and Code3. The values may differ in these columns but it will be always 2 Commas seperated repectivel. For Ex: 'E1,E2,E3' 'R1,R2, ' 'Y1,Y2,Y3'. Here for 'R1,R2, ' it will be R1 ..R2 ..BlankValue

Thom A
Thom A
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46182 Visits: 15686
This is a little bit of a "quick answer", as I'm sure there's faster, however, you could use the DelimitedSplit8K function:
SELECT t.StudentID,
t.StudentName,
C1.Item AS Code1,
C2.Item As Code2,
C3.Item AS Code3
FROM #tStudents t
CROSS APPLY dbo.DelimitedSplit8K(t.Code1, ',') C1
CROSS APPLY dbo.DelimitedSplit8K(t.Code2, ',') C2
CROSS APPLY dbo.DelimitedSplit8K(t.Code3, ',') C3
WHERE C1.ItemNumber = C2.ItemNumber AND C2.ItemNumber = C3.ItemNumber;



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
etirem
etirem
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 535
Thom A - Tuesday, December 12, 2017 5:59 AM
This is a little bit of a "quick answer", as I'm sure there's faster, however, you could use the DelimitedSplit8K function:
SELECT t.StudentID,
t.StudentName,
C1.Item AS Code1,
C2.Item As Code2,
C3.Item AS Code3
FROM #tStudents t
CROSS APPLY dbo.DelimitedSplit8K(t.Code1, ',') C1
CROSS APPLY dbo.DelimitedSplit8K(t.Code2, ',') C2
CROSS APPLY dbo.DelimitedSplit8K(t.Code3, ',') C3
WHERE C1.ItemNumber = C2.ItemNumber AND C2.ItemNumber = C3.ItemNumber;

Helpful...thank you.

aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6463 Visits: 1028
+1 for delimited split 8K
be aware though that you are doing a 3 way Cartesian join so with 3 elements you are selecting 1 from 27 combinations, with 4 elements you would be selecting 1 from 56, with 10 elements you will be selecting 1 in 1000 so the performance will go down hill quickly. If you have more than three fields then exponent will increase. 10 elements across 5 columns = 1 in 100000
HOWEVER:

If you have the opportunity to refactor your database to provide a proper 3NF form you will benefit greatly Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search