January 13, 2011 at 12:18 pm
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.
January 13, 2011 at 12:53 pm
How's this?
declare @test-2 varchar(100);
set @test-2 = '''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
January 13, 2011 at 1:43 pm
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'
January 13, 2011 at 2:17 pm
Expanding Waynes code:
DECLARE @test-2 TABLE (id INT, val VARCHAR(100));
INSERT INTO @test-2
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
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;
January 13, 2011 at 2:34 pm
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
January 13, 2011 at 2:38 pm
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
January 13, 2011 at 3:33 pm
since I already have a table to use, how can I put that in place of
DECLARE @test-2 TABLE (id INT, val VARCHAR(100));
INSERT INTO @test-2
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-2 here? if I put the table name in there but it doesnt work.
(
SELECT *
FROM
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.
January 13, 2011 at 3:44 pm
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.
January 13, 2011 at 3:45 pm
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-2 to "thetable"
that should get you going!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 13, 2011 at 4:01 pm
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;
January 13, 2011 at 4:02 pm
I should add that the table AWGCustom.dbo.COMS_PAYTABLE has more fields that I am using in this query. I just dont need them now so I skip them.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply