locked
How to Groupby with multiple repeated columns RRS feed

  • Question

  • User2046027549 posted

    Thanks in advance!

    So I have data something like this (the zeros and ones are switches so I need the max value not an aggregate)...

    UID Host Word Excel PowerPoint
    zce1121z 2UA00000Z 1 0 0
    zce1121z 2UA00000Z 0 1 0

    I need to "flatten" it like so...

    UID Host Word Excel PowerPoint
    zce1121z 2UA00000Z 1 1 0

    The problem is if I try to group them sql wants me to group the Host column as well... when I try to do it like so It's not flattening the rows.

    SELECT UID, Host, MAX(Word) Word, MAX(Excel) Excel, MAX(PowerPoint) PowerPoint

    FROM MyTable

    Group By UID, Host;

    Returns...

    UID Host Word Excel PowerPoint
    zce1121z 2UA00000Z 1 0 0
    zce1121z 2UA00000Z 0 1 0
    Tuesday, May 7, 2019 3:07 PM

All replies

  • User475983607 posted

    The problem is if I try to group them sql wants me to group the Host column as well... when I try to do it like so It's not flattening the rows.

    SELECT UID, Host, MAX(Word) Word, MAX(Excel) Excel, MAX(PowerPoint) PowerPoint

    FROM MyTable

    Group By UID, Host;

    It should work if the Host columns are the same.  Perhaps you have a space in one the columns.

    IF OBJECT_ID('tempdb..#Apps') IS NOT NULL DROP TABLE #Apps
    GO
    
    CREATE TABLE #Apps (UID VARCHAR(16), Host VARCHAR(16), Word INT, Excel INT, PowerPoint INT)
    GO
    
    INSERT INTO #Apps (UID, Host, Word, Excel, PowerPoint)
    VALUES ('zce1121z', '2UA00000Z',  1,0,0),
    	('zce1121z', ' 2UA00000Z', 0,1,0)
    
    SELECT UID, RTRIM(LTRIM(Host)) Host, MAX(Word) Word, MAX(Excel) Excel, MAX(PowerPoint) PowerPoint
    FROM #Apps
    GROUP BY UID, RTRIM(LTRIM(Host))

    Tuesday, May 7, 2019 3:20 PM