March 16, 2010 at 12:57 pm
Hi,
here is what i have.
Id, Code
1 A1, A2
2 B1, B2, B3
Here is what i need.
Id Code1 Code2 Code3
1 A1 A2
2 B1 B2 B3
So for "Code" colum that has comma seperated value (we can assume at most 30). For each value i need a seperate column. So for each row first comma seperated value will go to first column, second to the second and so on.
Any help would be appreciated.
Thanks.
March 16, 2010 at 1:22 pm
Step 1:
use a split string fuction to separate your comm separated list (for a sample function please see the tally table link in my signature).
Step 2:
Either use PIVOT or a CrossTab method to build your desired result set. An example for the first method can be found at BOL, and for the second one you'll find the related link in my signature as well.
March 17, 2010 at 5:40 am
I think Pivot is used for show aggregated data
in Your case test below mention code
DECLARE @T TABLE
(ID INT IDENTITY, NAME NVARCHAR(200));
INSERT INTO @T (NAME) VALUES ('A1, A2, A3');
INSERT INTO @T (NAME) VALUES ('B1, B2, B3');
INSERT INTO @T (NAME) VALUES ('C1, C2, C3');
INSERT INTO @T (NAME) VALUES ('D1, D2, D3');
INSERT INTO @T (NAME) VALUES ('E1, E2, E3');
--sELECT * FROM @T
SELECT id, LTRIM(RTRIM(i.value('.', 'VARCHAR(20)'))) AS [Name]
INTO #tmp
FROM
(
SELECT
iD, NAME,
CAST('<i>' + REPLACE(NAME, ',', '</i><i>') + '</i>' AS XML) AS ValXml
FROM @t
) a
CROSS APPLY ValXml.nodes('//i') x(i)
Select * from #tmp
Select a.id, Col1, Col2, Col3
from
(
SELECT Row_Number() OVER (ORDER BY ID) Row_N,ID, Name Col1
FROM #tmp
) a,
(
SELECT Row_Number() OVER (ORDER BY ID) Row_N,ID, Name Col2
FROM #tmp
) b,
(
SELECT Row_Number() OVER (ORDER BY ID) Row_N,ID, Name Col3
FROM #tmp
) c
where b.Row_N = a.Row_N+1
and c.Row_N = b.Row_N+1
and a.id = b.id
and b.id = c.id
March 17, 2010 at 6:31 am
No need to do the JOIN:ing in the last step in Vijays solution.
Alternative and more scalable solution (in terms of number of columns):
DECLARE @T TABLE
(ID INT IDENTITY, NAME NVARCHAR(200));
INSERT INTO @T (NAME) VALUES ('A1, A2, A3');
INSERT INTO @T (NAME) VALUES ('B1, B2, B3');
INSERT INTO @T (NAME) VALUES ('C1, C2, C3');
INSERT INTO @T (NAME) VALUES ('D1, D2, D3');
INSERT INTO @T (NAME) VALUES ('E1, E2, E3');
--sELECT * FROM @T
SELECT id, LTRIM(RTRIM(i.value('.', 'VARCHAR(20)'))) AS [Name], row_number() over (partition by id order by Name) as [ColNo]
INTO #tmp
FROM
(
SELECT
iD, NAME,
CAST('<i>' + REPLACE(NAME, ',', '</i><i>') + '</i>' AS XML) AS ValXml
FROM @t
) a
CROSS APPLY ValXml.nodes('//i') x(i)
Select * from #tmp
select id,
max(case when ColNo = 1 then [Name] else '' end) AS Col1,
max(case when ColNo = 2 then [Name] else '' end) AS Col2,
max(case when ColNo = 3 then [Name] else '' end) AS Col3,
max(case when ColNo = 4 then [Name] else '' end) AS Col4,
max(case when ColNo = 5 then [Name] else '' end) AS Col5,
max(case when ColNo = 6 then [Name] else '' end) AS Col6,
max(case when ColNo = 7 then [Name] else '' end) AS Col7,
max(case when ColNo = 8 then [Name] else '' end) AS Col8
/* add more columns here if needed */
from #tmp
group by id
/Markus
March 17, 2010 at 6:39 am
Yes You are right. Thankx for another solution
March 17, 2010 at 6:51 am
XML really is a very poor choice for splitting strings. It is hard to do worse.
See http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html for the results of some very extensive testing we did right here on SSC. The solution suggested by Lutz is far better.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 7:08 am
Yes Paul, But for Smaller String We can use.
XML also have validation as it don't support spacial character like "&"
/*
Name:
SplitString
Parameter:
1) Seperator
2) String
Details:
This function is used to split string with specified delimeter
Execution:
SELECT * FROM SplitString (',','A1234,A1235,A1256,PROTECH ENGINEERING & CONTROLS PVT,R.P SHAH & SONS')
*/
CREATE FUNCTION SplitString
(
@mseparator varchar(5),
@mstring varchar(8000)
)
RETURNS @splitstring table
(
id int identity(1,1),
splitstring varchar(50)
)
as
begin
declare
@mflag int,
@mpos int,
@msplittedstring varchar(8000),
@mlenseparator int,
@mlikeseparator varchar(10)
set @mflag = 1
set @mlenseparator = 1
set @mlikeseparator = ltrim(rtrim('%'+@mseparator+'%'))
while (@mflag = 1)
begin
set @mpos = patindex(@mlikeseparator,@mstring)
if (@mpos != 0)
begin
set @msplittedstring = substring(@mstring,@mlenseparator,@mpos-@mlenseparator)
set @mstring = substring(@mstring,@mpos+1,len(@mstring)-len(@msplittedstring)-1)
end
else
begin
set @msplittedstring = substring(@mstring,@mlenseparator,len(@mstring))
set @mflag = 0
end
insert @splitstring
select @msplittedstring
set @mlenseparator = len(@mseparator)
end
return
end
March 17, 2010 at 7:15 am
vijay.s (3/17/2010)
Yes Paul, But for Smaller String We can use.
:sick: XML string-splitting and a multi-statement user-defined table-valued function with a WHILE loop? :sick:
Ouch!
I'll say it again. XML is the wrong tool for the job. Use a tally table (as Lutz suggested) for small strings.
You could even get rid of that awful function and replace it with a nice in-line one!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 7:31 am
Here comes a Tally table solution (I just did a cut-and-paste of Jeff:s example and added the required commas in the beginning and end of the strings - the code could probably be (re)written a little neater if you put some more effort in it :-)):
DECLARE @T TABLE
(ID INT IDENTITY, NAME NVARCHAR(200));
INSERT INTO @T (NAME) VALUES ('A1, A2, A3');
INSERT INTO @T (NAME) VALUES ('B1, B2, B3');
INSERT INTO @T (NAME) VALUES ('C1, C2, C3');
INSERT INTO @T (NAME) VALUES ('D1, D2, D3');
INSERT INTO @T (NAME) VALUES ('E1, E2, E3');
SELECT id, LTRIM(SUBSTRING(','+[Name]+',', N+1, CHARINDEX(',', ','+[Name]+',', N+1)-N-1)) as [Name], row_number() over (partition by id order by Name) as [ColNo]
INTO #tmp
FROM @T cross join
(select top 4000 row_number() over (order by t1.id) AS N
from Master.dbo.SysColumns t1, Master.dbo.SysColumns t2
) Tally
WHERE N < LEN([Name])
AND SUBSTRING(','+[Name]+',', N, 1) = ',' --Notice how we find the comma
Select * from #tmp
select id,
max(case when ColNo = 1 then [Name] else '' end) AS Col1,
max(case when ColNo = 2 then [Name] else '' end) AS Col2,
max(case when ColNo = 3 then [Name] else '' end) AS Col3,
max(case when ColNo = 4 then [Name] else '' end) AS Col4,
max(case when ColNo = 5 then [Name] else '' end) AS Col5,
max(case when ColNo = 6 then [Name] else '' end) AS Col6,
max(case when ColNo = 7 then [Name] else '' end) AS Col7,
max(case when ColNo = 8 then [Name] else '' end) AS Col8
/* add more columns here if needed */
from #tmp
group by id
drop table #tmp
/Markus
March 17, 2010 at 7:53 am
Thank you Markus. Nice job.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply