Ali Tokgönül Ali Tokgönül -4 years ago 97
SQL Question

SQL Server FOR Loop

I'm new in SQL Server. I just want to write a Stored procedure for this :

enter image description here

I don't know how many rows are there in the table. I want to group

X
and
Z
column and put together
Y
column's text belonged to
X
and
Z
column.

I can make this with two loop. Is there any short way i can use in SQL Server.

Answer Source

Use STUFF String function :

CREATE TABLE #table(X VARCHAR(10), Y VARCHAR(10) , name VARCHAR(10))
INSERT INTO #table(X , name , Y )
SELECT 'a','as','b' UNION ALL
SELECT 'a','ad','b' UNION ALL
SELECT 'a','de','b' UNION ALL
SELECT 'b','aa','c' UNION ALL
SELECT 'b','ss','c' UNION ALL
SELECT 'e','ew','r' UNION ALL
SELECT 'e','w','r' UNION ALL
SELECT 'e','ss','r' UNION ALL
SELECT 'e','dd','r' 

SELECT T1.X X , STUFF( ( SELECT '  ' + name FROM #table T2 WHERE T1.X = T2.X 
AND T1.Y = T2.Y FOR XML PATH('') ) ,1,2,'') Y ,  T1.Y Z 
FROM #table T1
GROUP BY X , Y 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download