SQL MS Crosstable with two colkey with repeated cases

1

Please login or register to vote for this query.

(click on this box to dismiss)

Amateur Radio

Q&A for amateur radio enthusiasts

CREATE TABLE #yt
([MyID] int, [Store] nvarchar(300), [City] nvarchar(300), [Week] int, [xCount] int, [yCount] int)
;

INSERT INTO #yt
    ([MyID], [Store], [City], [Week], [xCount], [yCount])
VALUES
    (1, 'L', 'C1', 1, 96, 7),
    (2, 'L', 'C1', 1, 138, 77),
    (3, 'L2', 'C1', 1, 37, 744),
    (4, 'L', 'C1', 1, 59, 74),
    (5, 'L', 'C1', 2, 282,73333),
    (6, 'L2', 'C2', 2, 212,7333),
    (7, 'L2', 'C2', 2, 78,733),
    (8, 'L', 'C2', 2, 97,73),
    (9, 'L', 'C2', 3, 60,72222),
    (10, 'L2', 'C2', 3, 123,7222),
    (11, 'L2', 'C1', 3, 220,722),
    (12, 'L2', 'C1', 3, 87,72)
;


select [MyId], [Week], [LC1_xCount], [LC2_xCount], [L2C1_xCount], [L2C2_xCount]
    , [LC1_yCount], [LC2_yCount], [L2C1_yCount], [L2C2_yCount]
from 
(
select myid, week, store, city, xcount, ycount,
    
    CASE WHEN
        [City]='C1' AND [Store]='L'
    THEN
        [xCount] 
    END
    AS 'LC1_xCount',

    CASE WHEN
        [City]='C2' AND [Store]='L'
    THEN
        [xCount] 
    END
    AS 'LC2_xCount',

    CASE WHEN
        [City]='C1' AND [Store]='L2'
    THEN
        [xCount] 
    END
    AS 'L2C1_xCount',

    CASE WHEN
        [City]='C2' AND [Store]='L2'
    THEN
        [xCount] 
    END
    AS 'L2C2_xCount',


    CASE WHEN
        [City]='C1' AND [Store]='L'
    THEN
        [yCount] 
    END
    AS 'LC1_yCount',

    CASE WHEN
        [City]='C2' AND [Store]='L'
    THEN
        [yCount] 
    END
    AS 'LC2_yCount',

    CASE WHEN
        [City]='C1' AND [Store]='L2'
    THEN
        [yCount] 
    END
    AS 'L2C1_yCount',

    CASE WHEN
        [City]='C2' AND [Store]='L2'
    THEN
        [yCount] 
    END
    AS 'L2C2_yCount'






  from #yt
  GROUP BY myid, week, store,city, xcount, ycount
) src;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results