How to convert rows into columns...

  • 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.

  • Hi

    This link will solve your prob

    http://bytes.com/topic/sql-server/answers/531936-how-convert-rows-into-columns

  • 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.

  • 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

  • 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