CODEWITHSUNDEEP
sqlsql-server
BobNoobGuy
BobNoobGuy

Reputation: 1645

How to show query result in group? SQL Query

Right now my sql query display the result as follows. though it is the correct result.

enter image description here

I prefer to have the have the result to show as follows. How can I do this with SQL ? I am on SQL server 2008

enter image description here

Upvotes: 0

Views: 85

Answers (2)

Hart CO
Hart CO

Reputation: 34784

I'm with the commenters, better to do this elsewhere, but it's simple enough in SQL using a CASE statement and the ROW_NUMBER() function:

;WITH cte AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) RN
              FROM  YourTable)
SELECT CASE WHEN RN = 1 THEN CAST(ID AS VARCHAR(5)) ELSE '' END, Name
FROM cte
ORDER BY ID,RN

Demo: SQL Fiddle

Upvotes: 3

Yosi Dahari
Yosi Dahari

Reputation: 7009

This is not a job for SQL.
Any way, you can easily display it with comma separated values:

 ID   Names
1000  Honda, Toyota,...
1000  Honda, Toyota,...

SELECT ID, Names= 
    STUFF((SELECT ', ' + Name
           FROM your_table b 
           WHERE b.ID= a.ID
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ID

Upvotes: 1

Related Questions