TSQL Pivot Crosstab

  • Hi,

    I am doing a crosstab but I am unsure if its a pivot or is there a simpler way of doing it.

    Create Table  TblPerson (ID Int, Name Varchar(50), Child Varchar(50));

    SELECT *  FROM TblPerson  ID,  Name,  Child

     

    The output should look like this, not everyone has equal number of items so some condition fields will be blank!

     

    Some people may have 1 or many conditions.

    All help appreciated please.

     

    Thank you.

     

    • This topic was modified 8 months, 4 weeks ago by  Tallboy.
  • Deneyin.

    DECLARE @TBLPERSON TABLE 
    (
    ID INT,
    NAME NVARCHAR(100),
    CONDITION NVARCHAR(100)
    )

    INSERT INTO @TBLPERSON (ID,NAME,CONDITION)
    VALUES ('1','Mary','DrySkin'),('2','Bob','DrySkin'),('3','Mary','Glasses'),('4','Bob','Glasses'),('5','Joe','Glasses'),('6','Mary','GreyHair'),('7','Bob','Glasses'),('8','Mary','Hearing'),('9','Joe','Hearing')

    ;WITH DETAY AS
    (
    select *,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID) Sıra
    from @TBLPERSON
    )


    SELECT NAME,(SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=1) AS CONDITION1,
    (SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=2) AS CONDITION2,
    (SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=3) AS CONDITION3,
    (SELECT F.CONDITION FROM DETAY F WHERE F.NAME = DETAY.NAME AND F.Sıra=4) AS CONDITION4
    FROM DETAY
    GROUP BY NAME
    ORDER BY NAME

    Allah bize yeter, O ne güzel vekildir.

    vedatoozer@gmail.com

  • Hi Vedat,

     

    That looks great thank you, I will try it out, but is it limited to 4 conditions? as there could be many more per person!

     

     

  • ;WITH Cte_Persons AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Id) AS Row_Num
    FROM @TBLPERSON
    )
    SELECT
    Name,
    MAX(CASE WHEN row_num = 1 THEN Condition END) AS Condition1,
    MAX(CASE WHEN row_num = 2 THEN Condition END) AS Condition2,
    MAX(CASE WHEN row_num = 3 THEN Condition END) AS Condition3,
    MAX(CASE WHEN row_num = 4 THEN Condition END) AS Condition4,
    MAX(CASE WHEN row_num = 5 THEN Condition END) AS Condition5,
    MAX(CASE WHEN row_num = 6 THEN Condition END) AS Condition6,
    MAX(CASE WHEN row_num = 7 THEN Condition END) AS Condition7,
    MAX(CASE WHEN row_num = 8 THEN Condition END) AS Condition8,
    /* ... */
    FROM Cte_Persons
    GROUP BY Name

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher very nice solution

    Allah bize yeter, O ne güzel vekildir.

    vedatoozer@gmail.com

  • Looks great Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply