CODEWITHSUNDEEP
sql-serverhierarchy
Syafrizal
Syafrizal

Reputation: 23

mssql hierarchy one row to multiple column query

I have a table with structure and record like this

________________________________________
ID          | Name              |
-----------------------------------------
01          | Group Category    |
0101        | Category      |
010101      | Category Sub      |
01010101    | Category Sub Sub  |
-----------------------------------------

How to make a query result with mssql like bellow.

Column1         | Column2       | Column3       | Column4           |
-------------------------------------------------------------------------
Group Category  | Category Sub  | Category Sub  | Category Sub Sub  |
-------------------------------------------------------------------------

Upvotes: 0

Views: 136

Answers (2)

George T
George T

Reputation: 857

You could also PIVOT your table like this:

SELECT [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4
FROM
(
    SELECT 
        Name, 
        ROW_NUMBER() OVER(ORDER BY ID) Seq 
    FROM [Table]
) t
PIVOT
(
    MAX(Name)
    FOR Seq IN ([1], [2], [3], [4])
) p

Upvotes: 0

mohan111
mohan111

Reputation: 8865

declare @t table (ID varchar(10),name varchar(20))
insert into @t (ID,name) values ('01','Group Category'),('0101','Category'),
('010101','Category Sub'),('01010101','Category Sub Sub ')



select 
MAX(case when ID = '01' THEN name ELSE '' END )AS Column1,
MAX(case when ID = '0101' THEN name ELSE '' END )AS Column2,
MAX(case when ID = '010101' THEN name ELSE '' END )AS Column3,
MAX(case when ID = '01010101' THEN name ELSE '' END )AS Column4 
from @t

Upvotes: 1

Related Questions