InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:36:38 GMT20http://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxI 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.Thu, 13 Jan 2011 16:02:39 GMTdandensonhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxthe 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:[quote]Msg 207, Level 16, State 1, Line 8Invalid column name 'val'.[/quote]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.[quote]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;[/quote]Thu, 13 Jan 2011 16:01:30 GMTdandensonhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxChange:(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!Thu, 13 Jan 2011 15:45:03 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxThe 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 [code="sql"]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;[/code] 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.Thu, 13 Jan 2011 15:44:10 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxsince 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 @testSELECT 1,'jack,25,A67,91J,67,99' UNION ALLSELECT 2,'jill,5,8,V3' UNION ALLSELECT 3,'john,7,D4' UNION ALLSELECT 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.Thu, 13 Jan 2011 15:33:03 GMTdandensonhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspx[quote][b]LutzM (1/13/2011)[/b][hr]Expanding Waynes code:[/quote]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".Thu, 13 Jan 2011 14:38:25 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspx[quote][b]dandenson (1/13/2011)[/b][hr]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?[/quote]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.[quote]I havent seen this formula before (im kinda new), what would this be called? (the top part, I get the Cartesian join) [/quote] A kludge to work with data not properly normalized. ;-):-DThu, 13 Jan 2011 14:34:21 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxExpanding Waynes code:[code="sql"]DECLARE @test TABLE (id INT, val VARCHAR(100));INSERT INTO @testSELECT 1,'jack,25,A67,91J,67,99' UNION ALLSELECT 2,'jill,5,8,V3' UNION ALLSELECT 3,'john,7,D4' UNION ALLSELECT 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;[/code]Thu, 13 Jan 2011 14:17:34 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxThanks 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'Thu, 13 Jan 2011 13:43:51 GMTdandensonhttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxHow's this?[code="sql"]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;[/code] Click [url=http://www.sqlservercentral.com/Forums/FindPost944589.aspx][u]here[/u][/url] for the latest Delimited Split Function.Thu, 13 Jan 2011 12:53:17 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1047509-391-1.aspxsimply 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.Thu, 13 Jan 2011 12:18:37 GMTdandenson