May 30, 2014 at 9:15 am
Good day forumites, I have a unique request here.
I have the following table
CREATE Table #Table1
(
ID INT, Name VARCHAR(50), Class VARCHAR(10)
)
INSERT INTO #Table1
Select 1, 'name1', 'a' UNION ALL
Select 2, 'name1', 'a' UNION ALL
Select 3, 'name2', 'b' UNION ALL
Select 4, 'name2', 'b' UNION ALL
Select 5, 'name2', 'c' UNION ALL
Select 6, 'name2', 'c' UNION ALL
Select 7, 'name2', 'd' UNION ALL
Select 8, 'name2', 'd' UNION ALL
Select 9, 'name3', 'e' UNION ALL
Select 10, 'name3', 'f' UNION ALL
Select 11, 'name3', 'f' UNION ALL
Select 12, 'name3', 'f'
Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?
CREATE Table #Table2
(
ID INT, CommaSeparated VARCHAR(100)
)
INSERT INTO #Table2
Select 1, 'name1, a' UNION ALL
Select 2, 'name2, b, c, d' UNION ALL
Select 3, 'name3, e, f' UNION ALL
Select 4, NULL UNION ALL
Select 5, NULLUNION ALL
Select 6, NULLUNION ALL
SELECT 7, NULLUNION ALL
SELECT 8, NULLUNION ALL
SELECT 9, NULLUNION ALL
SELECT 10, NULLUNION ALL
SELECT 11, NULLUNION ALL
SELECT 12, NULL
what I have
Select * FROM #Table1
Final Result
Select * FROM #Table2
Note that I still want to see all the IDs regardless.
If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.
CREATE Table #Table3
(
CommaSeparated VARCHAR(100)
)
INSERT INTO #Table3
Select 'name1, a' UNION ALL
Select 'name2, b, c, d' UNION ALL
Select 'name3, e, f'
Select * FROM #Table3
Thanks for your time.
May 30, 2014 at 9:29 am
That's a really weird requirement. I can't see why would you want to do this but here's an option.
WITH ConcValues AS(
SELECT ROW_NUMBER() OVER( ORDER BY Name) AS rn,
CommaSeparated = Name + CONVERT(varchar(max),(SELECT ', ' +Class
FROM #Table1 x
WHERE t1.Name = x.Name
GROUP BY Class
ORDER BY Class
FOR XML PATH('')))
FROM #Table1 t1
GROUP BY Name
)
SELECT t1.ID,
cv.CommaSeparated
FROM #Table1 t1
LEFT
JOIN ConcValues cv ON t1.ID = cv.rn
For the comma separated values, check the following article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
May 30, 2014 at 9:51 am
Here's another way:
with cte as (
select rn=ROW_NUMBER() over(order by name)
, CommaSeparated = max(t1.name + t2.classlist)
from #table1 t1
cross apply (
select ', ' +t2.class
from #table1 t2
where t1.name = t2.name
group by class
for xml path('')) t2(classlist )
group by t1.name
)
select * from cte
union all
select top((select count(*) from #table1) - (select count(*) from cte))
ROW_NUMBER() over(order by (select 1))+(select count(*) from cte)
, null
from #Table1
June 3, 2014 at 1:16 pm
The first solution worked correctly. Thank you guys for both solutions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply