Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

csv unpivot WITH identifying column Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 12:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:25 AM
Points: 24, Visits: 44
simply put, I have this in a table:

username, salesnumbers
'jack','25,A67,91J'

and I need

username, salesnumber
'jack','25'
'jack','A67'
'jack','91J'

I have found a few examples of just unpivoting the csv values, but none that include the 'key' column.
Post #1047509
Posted Thursday, January 13, 2011 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 6,545, Visits: 8,763
How's this?
declare @test varchar(100);
set @test = '''jack'',''25,A67,91J''';
WITH CTE AS
(
SELECT *
FROM dbo.DelimitedSplit8K(@test, ',')
)
SELECT UserName = CTE1.Item, SaleNumber = Replace(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))
FROM CTE CTE1
CROSS JOIN (SELECT Item FROM CTE WHERE ItemNumber > 1) CTE2
WHERE CTE1.ItemNumber = 1;


Click here for the latest Delimited Split Function.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1047533
Posted Thursday, January 13, 2011 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:25 AM
Points: 24, Visits: 44
Thanks Wayne, your code works wonderfully. I do have a few questions though.

First, I'm trying to learn/understand this. I get that ItemNumber comes from the DelimitedSplit8K function (which I have and is working). Am I right in guessing that the 'where cte1.ItemNumber =1' is just to select the first row from the returned delimitedsplit8k which is now in a temp table called CTE aliased CTE1. Then a cross join of CTE aliased to CTE2 without the first row so the cross join is doing the work of taking the second and higher values from the CSV aka rows 2+ from the CTE table and gluing the 'Username labeled, row 1 Item field?

I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join)

Also, This assumes I have just one row of data to 'unpivot', how can I apply this function to a table of data? (which is about 30 lines long, but will expand to several hundred)?

example if the question was poorly asked:
"Username + ',' + csv values" = 'jack,25,A67,91J,35,67,99', so the table is like:
'jack,25,A67,91J,67,99'
'jill,5,8,V3'
'john,7,D4'
'bill,66,84'

result:
'jack','25'
'jack','A67'
'jack','91J'
'jack','67'
'jack','99'
'jill','5'
'jill','8'
'jill','V3'
'john','7'
'john','D4'
'bill','66'
'bill','84'
Post #1047558
Posted Thursday, January 13, 2011 2:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 6,938, Visits: 12,677
Expanding Waynes code:
DECLARE @test TABLE (id INT, val VARCHAR(100));
INSERT INTO @test
SELECT 1,'jack,25,A67,91J,67,99' UNION ALL
SELECT 2,'jill,5,8,V3' UNION ALL
SELECT 3,'john,7,D4' UNION ALL
SELECT 4,'bill,66,84';

WITH CTE AS
(
SELECT *
FROM
@test
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x
)
SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))
FROM CTE CTE1
INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id
WHERE CTE1.ItemNumber = 1;





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1047581
Posted Thursday, January 13, 2011 2:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 6,545, Visits: 8,763
dandenson (1/13/2011)
Thanks Wayne, your code works wonderfully. I do have a few questions though.

First, I'm trying to learn/understand this. I get that ItemNumber comes from the DelimitedSplit8K function (which I have and is working). Am I right in guessing that the 'where cte1.ItemNumber =1' is just to select the first row from the returned delimitedsplit8k which is now in a temp table called CTE aliased CTE1. Then a cross join of CTE aliased to CTE2 without the first row so the cross join is doing the work of taking the second and higher values from the CSV aka rows 2+ from the CTE table and gluing the 'Username labeled, row 1 Item field?


First, I'm glad it's doing what you want... well, almost.

Yes, the ItemNumber does come from the DelimitedSplit8k function. It is sequential by the position of the data in the delimited string. In this case, the first item in the string is the UserName, so we are looking for the ItemNumber=1.

You are correct about what the CROSS JOIN is doing.

I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join)
A kludge to work with data not properly normalized.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1047587
Posted Thursday, January 13, 2011 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 6,545, Visits: 8,763
LutzM (1/13/2011)
Expanding Waynes code:


Thanks Lutz!

@dandenson - in the first post, you had your expected results encased in single quotes; in your subsequent post you took them out. That whole replace function is because you originally specified it. If you don't want them encased in single-quotes, then change the entire replace function with simply "CTE2.Item".


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1047589
Posted Thursday, January 13, 2011 3:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:25 AM
Points: 24, Visits: 44
since I already have a table to use, how can I put that in place of

DECLARE @test TABLE (id INT, val VARCHAR(100));
INSERT INTO @test
SELECT 1,'jack,25,A67,91J,67,99' UNION ALL
SELECT 2,'jill,5,8,V3' UNION ALL
SELECT 3,'john,7,D4' UNION ALL
SELECT 4,'bill,66,84';


I tried to just put (select row_number() over (order by name) as id, name + ',' + salesnumbers from thetable;

but I cant see what to put in place of the @test here? if I put the table name in there but it doesnt work.
(
SELECT *
FROM
@test
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x
)

I did try it with the declare as is just replacing the select lines with my single select and it works, but looks a bit odd.

otherwise, I am getting the desired results.

Thanks a lot guys.
Post #1047625
Posted Thursday, January 13, 2011 3:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 6,938, Visits: 12,677
The temp table has been used to get a code block for demonstration purposes.
Assuming you're last statement will end with a semicolon, you could simply use
WITH CTE AS
(
SELECT *
FROM
YourTableNameGoesHere
CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8K(val, ',')) x
)
SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))
FROM CTE CTE1
INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id
WHERE CTE1.ItemNumber = 1;

and replace YourTableNameGoesHere with your actual table name.
If there are any errors, they'd be most like due to different column names. If not, post the full error message andwe'll see if we can assist you any further.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1047632
Posted Thursday, January 13, 2011 3:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 6,545, Visits: 8,763
Change:
(select row_number() over (order by name) as id, name + ',' + salesnumbers from thetable;

to
(select row_number() over (order by name) as id,
val = name + ',' + salesnumbers from thetable;

Change @test to "thetable"

that should get you going!


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1047633
Posted Thursday, January 13, 2011 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:25 AM
Points: 24, Visits: 44
the actual query. the Com* columns added together are the key, I add a ',' and attach it to salnum which is the CSV value. If I put the full table path in to the subselect that shows COMS_PAYTABLE below, it errors:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'val'.


this makes sence because the 'val' column is an concatenation. If I replace the select * with select ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val, * I still get the same Invalid column name 'val'. Same if I take the entire select statement from above and put there.

select row_number() over (order by comgroup) as id,ComGroup+GroupCategory+GroupType+ ',' +SALNUM as val from AWGCustom.dbo.COMS_PAYTABLE;
WITH CTE AS
(
SELECT *
FROM
AWGCustom.dbo.COMS_PAYTABLE
CROSS APPLY
(SELECT * FROM AWGCustom.dbo.DelimitedSplit8K(val, ',')) x
)
SELECT UserName = CTE1.Item, SaleNumber = REPLACE(CHAR(39) + CTE2.Item + CHAR(39), CHAR(39)+CHAR(39), CHAR(39))
--into #temp
FROM CTE CTE1
INNER JOIN (SELECT Item,id FROM CTE WHERE ItemNumber > 1 ) CTE2 ON CTE1.id=CTE2.id
WHERE CTE1.ItemNumber = 1;
Post #1047645
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse