Geek asks and answers

Member in multiple hierarchies (forest?) In one table

if I have the following table:

Member_Key  Member_Name             col1    Mem1    col2    Mem2    col3    Mem3    col4    Mem4
-----------------------------------------------------------------------------------------------
31          100.00 - Some Account   9       Rollup1 268     Rollup2 246     Rollup3 238   Rollup4
31          100.00 - Some Account   270     Rollup5 271     Rollup6 NULL    NULL    NULL    NULL
31          100.00 - Some Account   283     Rollup7 NULL    NULL    NULL    NULL    NULL    NULL

Required Result:

Member_Key    Member_Name    col1  mem1  col2  mem2  col3  mem3 col4 mem4 col5 mem5  col6  mem6  col7  mem7

I try to do this with 1 account, to try to make it dynamic for x the number of accounts that have the number of drives, and put them in one result of the variable column, but I can't seem to make it work with PIVOT or SELECT CASE . Any help or links would be greatly appreciated.

+3
source share
1 answer

I hope the below request is your answer:

SELECT *
From
    (SELECT  A.Member_Key,
            A.Member_Name,
            CASE B.n
                WHEN 1 THEN A.Mem1
                WHEN 2 THEN A.Mem2
                WHEN 3 THEN A.Mem3
                WHEN 4 THEN A.Mem4
            END AS Mem,
            CASE B.n
                WHEN 1 THEN A.Col1
                WHEN 2 THEN A.Col2
                WHEN 3 THEN A.Col3
                WHEN 4 THEN A.Col4
            END AS Col
        FROM MyTable A
        CROSS JOIN (SELECT 1 
            UNION SELECT 2
            UNION SELECT 3
            UNION SELECT 4)B(n)
    )AS p
PIVOT (MIN(Col) FOR Mem IN ([Rollup1], [Rollup2], [Rullup3], [Rollup4], [Rollup5], [Rullup6], [Rullup7])) AS Pvt
0
source

More articles:


All Articles