Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


csv unpivot WITH identifying column


csv unpivot WITH identifying column

Author
Message
dandenson
dandenson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
Author - SQL Server T-SQL Recipes
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

dandenson
dandenson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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'
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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. ;-):-D

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
Author - SQL Server T-SQL Recipes
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

dandenson
dandenson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
Author - SQL Server T-SQL Recipes
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

dandenson
dandenson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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;

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