Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

TSQL Query: Group Multiple Rows into one column

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
Netstore
Asked:
Netstore
2 Solutions
 
DireOrbAntCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now