February 9, 2011 at 11:23 pm
Hi,
I have a table like
CREATE TABLE [dbo].[ReportCodeDim](
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int] NULL,
[Isdeleted] [bit] NULL)
Here id is PK and ParentId is FK references Id (ie., Parent child relationship).
Sample data:
IdNameParentIdIsdeleted
1ProductsNULLNULL
2Bikes1NULL
3Touring Bikes1NULL
4Road Bikes1NULL
5AccessoriesNULLNULL
6Helmets5NULL
7Hydration Packs5NULL
8Lights5NULL
9Tires and Tubes5NULL
Now i wish to display the data like
Id(Identity) Products Accessories
1 Bikes Helmets
2 Touring Bikes Hydration Packs
3 Road Bikes Lights
4 NULL Tires and Tubes
Please suggest...
Thanks,
Suresh.
February 9, 2011 at 11:39 pm
Hi
This link will solve your prob
http://bytes.com/topic/sql-server/answers/531936-how-convert-rows-into-columns
February 10, 2011 at 12:05 am
This link may help you:
http://bytes.com/topic/sql-server/answers/531936-how-convert-rows-into-columns
February 10, 2011 at 12:08 am
thanks for your reply...
but here it has parent child relation and i am able to display the parents as columns but data that is childs are not updating...
please suggest me.
thanks,
Suresh.
February 10, 2011 at 1:19 am
Something like this?
WITH CTE_HIER AS
(
SELECT [Id],[Name] , [ParentId], CAST('' AS VARCHAR(50)) AS ParentName
FROM [ReportCodeDim]
WHERE [ParentId] IS NULL
UNION ALL
SELECT SRC.[Id],SRC.[Name] , SRC.[ParentId], REC.[Name] As ParentName
FROM [ReportCodeDim] SRC
JOIN CTE_HIER REC
ON SRC.[ParentId] = REC.[Id]
)
,PIVOTED AS
(
SELECT
RN = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) ,
(CASE WHEN 'Products' = ParentName THEN Name END ) Products ,
(CASE WHEN 'Accessories' = ParentName THEN Name END ) Accessories
FROM CTE_HIER
WHERE ParentName <> ''
)
SELECT * FROM PIVOTED
February 10, 2011 at 1:36 am
Or rather, this ?
WITH CTE_HIER AS
(
SELECT [Id],[Name] , [ParentId], CAST('' AS VARCHAR(50)) AS ParentName
FROM [ReportCodeDim]
WHERE [ParentId] IS NULL
UNION ALL
SELECT SRC.[Id],SRC.[Name] , SRC.[ParentId], REC.[Name] As ParentName
FROM [ReportCodeDim] SRC
JOIN CTE_HIER REC
ON SRC.[ParentId] = REC.[Id]
)
,Pivoted AS
(
SELECT
(CASE WHEN 'Products' = ParentName THEN Name END ) Products ,
(CASE WHEN 'Accessories' = ParentName THEN Name END ) Accessories
FROM CTE_HIER
PIVOT
WHERE ParentName <> ''
)
SELECT
Products
,Accessories
FROM
(
SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) ,
Products
FROM Pivoted
WHERE Products IS NOT NULL
) P
FULL OUTER JOIN
(
SELECT RN = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) ,
Accessories
FROM Pivoted
WHERE Accessories IS NOT NULL
) A
ON
P.RN = A.RN
This is a very ugly way of doing it.. probably some Gurus might help you with high performing fantastic code..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply