CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ntalekt
ntalekt

Reputation: 852

Joining multiple columns into one row

Using MSSQL 2008 and having troubles returning the rows the way I need it. The following query returns the following data.

Query:

SELECT [DATE_PULL] as d, [OWNER] as label, sum(VM_COUNT) as value 
FROM [VCENTER_INFO_HIST] 
GROUP BY OWNER, DATE_PULL

+-------------------------------------------------+
|    D       |   label        |  value            |
+-------------------------------------------------+
| 06/30/2014 |    Test1       |       443         |
+-------------------------------------------------+
| 06/30/2014 |    Test2       |       456         |
+-------------------------------------------------+
| 06/30/2014 |    Test3       |       487         |
+-------------------------------------------------+
| 07/07/2014 |    Test1       |       1024        |
+-------------------------------------------------+
| 07/07/2014 |    Test2       |       2056        |
+-------------------------------------------------+
| 07/07/2014 |    Test3       |       2076        |
+-------------------------------------------------+

I need the output to look like this:

+---------------------------------------------------+
|    D       |                value                 |
+---------------------------------------------------+
| 06/30/2014 | Test1: 443, Test2: 456, Test3: 487   |
+---------------------------------------------------+
| 07/07/2014 | Test1: 1024, Test2: 2056, Test3: 2076|
+---------------------------------------------------+

Any help would be appreciated!

Upvotes: 0

Views: 79

Answers (1)

entropic
entropic

Reputation: 1683

You can use the STUFF and FOR XML PATH abilities in SQL Server like so: 

select distinct [DATE_PULL] as D, 
    stuff((select ', ' + [OWNER] + ': ' + convert(varchar(20), sum(VM_COUNT)) + '' as [text()]
                from [VCENTER_INFO_HIST] c where c.[DATE_PULL] = v.[DATE_PULL]
                group by c.[OWNER] 
                for xml path('')), 1, 1, '') as value
    from [VCENTER_INFO_HIST] v

Keep in mind, as the data set grows, this might be a bit inefficient since we are doing a sub query, but it's the only way I know how to do something like you stated above. If there's another way, someone kindly point me in the right direction as well....

Anyway, you can find more information about STUFF here and the FOR XML here. Although, if you want them to be independent columns, the answer Kris provided in the comments with the use of PIVOT is more likely what you would want.

Upvotes: 3

Related Questions