Solved

TSQL Query: Group Multiple Rows into one column

Posted on 2006-07-18
4
207 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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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 …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now