Solved

TSQL Query: Group Multiple Rows into one column

Posted on 2006-07-18
4
210 Views
Last Modified: 2012-08-13
I'm trying to create a query to from the example table below, which will group by column 1 but for every matching value in column 1 group together the column 2 values into a single string.

Column 1, Column 2
Server A, Ping
Server A, DNS
Server A, HTTP
Server B, Ping
Server B, HTTP
etc.


The end game would be this result (to then be passed to a asp.net vb page gridview for displaying, I'm trying via the sql query as the gridview doesn't do grouping)

Column 1, Column 2
Server A, (Ping, DNS, HTTP)
Server B, (Ping, HTTP)

ta
0
Comment
Question by:Netstore
4 Comments
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 125 total points
ID: 17132726
DECLARE @MergedTable TABLE (PrimaryKey INT IDENTITY, [Column 1] VARCHAR(50), [Column 2] VARCHAR(1000))
DECLARE @Count INT, @Col2List VARCHAR(1000)

INSERT INTO @MergedTable ([Column 1])
  SELECT DISTINCT [Column 1] FROM YourTable

SET @Count = 1
WHILE @Count <= (SELECT MAX(PrimaryKey) FROM @MergedTable)
BEGIN
  SET @Col2List = NULL

  SELECT @Col2List = COALESCE(@Col2List + ', ', '') + Y.[Column 2]
  FROM YourTable Y
  JOIN @MergedTable M ON M.[Column 1] = Y.[Column 1]
  WHERE M.PrimaryKey = @Count
     
  UPDATE @MergedTable SET [Column 2] = @Col2List WHERE PrimaryKey = @Count
  SET @Count = @Count + 1
END

SELECT [Column 1], [Column 2] FROM @MergedTable
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 125 total points
ID: 17135325
create a function like this

CREATE function dbo.RetCSVs (
@Column1 varchar(100)
)
Returns Varchar(8000)
AS
BEGIN
    declare @out Varchar(8000)
    SELECT @out = COALESCE (RTRIM(@out)+',','')+Column2
    FROM urTable ------------ replace  this with ur table name
    WHERE Column1 = @Column1

    return @out
END
GO

And call like

SELECT Column1, dbo.RetCSVs(Column1) As Column2
FROM urTable
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question