5

SQL Server 2005

I have a table which returns

ID  name    prop    value
--------------------------
1   one     Prop1   a
1   one     Prop1   b
1   one     Prop2   c
2   two     Prop1   d
2   two     Prop2   e

How can I run a select on it to return

ID  name    prop        value
-----------------------------
1   one     Prop1       a,b
1   one     Prop2       c
2   two     Prop1       d
2   two     Prop2       e
Omar
  • 39,496
  • 45
  • 145
  • 213
  • How did you get `ID` of 4 in the last row of your desired output? – JNK Jun 20 '11 at 15:30
  • 1
    Take a look http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 . – a1ex07 Jun 20 '11 at 15:31

3 Answers3

3

try this:

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, RowName varchar(5), prop varchar(5), RowValue varchar(5))

insert into @YourTable VALUES (1,'one','Prop1','a')
insert into @YourTable VALUES (1,'one','Prop1','b')
insert into @YourTable VALUES (1,'one','Prop2','c')
insert into @YourTable VALUES (2,'two','Prop1','d')
insert into @YourTable VALUES (2,'two','Prop2','e')
set nocount off

SELECT
    t1.RowID,t1.RowName,t1.Prop
        ,STUFF(
                   (SELECT
                        ', ' + t2.RowValue
                        FROM @YourTable t2
                        WHERE t1.RowID=t2.RowID AND t1.RowName=t2.RowName AND t1.Prop=t2.Prop
                        ORDER BY t2.RowValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.RowID,t1.RowName,t1.Prop

OUTPUT:

RowID       RowName Prop  ChildValues
----------- ------- ----- ------------
1           one     Prop1 a, b
1           one     Prop2 c
2           two     Prop1 d
2           two     Prop2 e

(4 row(s) affected)
KM.
  • 101,727
  • 34
  • 178
  • 212
  • using xml to concatenate rows can be problematic if the values contain any "special" characters like `&`, `<`, `>` etc. The code in my answer will properly handle all special characters. For example, if the last column in the table above were to have a value of `a&` in it, this code will properly display `a&`, most other XML concatenation code that I've seen will erroneously display `a&`. – KM. Jun 20 '11 at 15:46
2
SELECT ID
    ,name
    ,prop
    ,STUFF((SELECT ',' + value FROM table t2 WHERE t2.ID = t1.ID and t2.name = t1.name AND t2.prop = t1.prop FOR XML PATH('')),1,1,'') AS value
FROM table t1
GROUP BY ID,name,prop

Please refer: SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

Community
  • 1
  • 1
EricZ
  • 6,065
  • 1
  • 30
  • 30
0
SELECT T1.Name, T1.prop, T1.value+","+T2.value
From Table T1 INNER JOIN Table T2
ON T1.name = T2.name and T1.prop = T2.Prop and T1.value<>T2.value

This will only work for two values though. Let me know and I can rewrite for N values.

therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • 1
    I didn't DV but I don't think this is what he is after. I don't think he wants to manually determine how many values there are and write code for that many occurences. – JNK Jun 20 '11 at 15:38
  • Yes, I expect N number of values. – Omar Jun 20 '11 at 15:43