Solved

TSQL Query: Group Multiple Rows into one column

Posted on 2006-07-18
4
211 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 41
SSIS On fail action 5 38
Getting invalid Syntax SQL. 3 21
SQL - Simple Pivot query 8 15
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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