alex paolo alex paolo - 6 months ago 10
SQL Question

Combine multiple rows with duplicate id

My table is

odb.OPMdata


columns = id, element, budget, month


i want it todo is

this sample sql query is not displaying all row with
Id='1'
only the MAX and MIN of it.

SELECT month, MAX(budgetmonth) AS value1, MAX(actual) AS value2, MAX(variance) AS value3,
MIN(budgetmonth) AS value4, MIN(actual) AS value5, MIN(variance) AS value6
FROM OPMdata
GROUP BY month


my problem is how to join the three or more duplicate rows to a single row, and the value will be display in each column.

i want to achieved is this.

id | value1 | value2 | value3 | value4 |value5| value 6 | value7 | value8 |val9

1 | CC | 100 | january | FF | 100 |february | DD | 200 |mar


and so on for other rows with the same id.
please help on this and thanks in advance.

Answer

You can try triple join like this + month table to order things right:

;WITH months AS ( -- Table with months for ordering
SELECT DATENAME(month,'1970-01-01') as [month], 1 as [level]
UNION ALL
SELECT DATENAME(month,DATEADD(month,[level],'1970-01-01')), [Level]+1
FROM months
WHERE [level] < 12
)
, results AS ( --Temp cte with add of ROW_NUMBERS()
SELECT  id, 
        element, 
        budget, 
        o.[month],
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY m.[level]) as rn
FROM OPMdata o
LEFT JOIN months m
    ON m.[month] = o.[month]
)
--Final query:
SELECT TOP 1 WITH TIES 
        r.id,
        r.element,
        r.budget,
        r.[month],
        r1.element,
        r1.budget,
        r1.[month],
        r2.element,
        r2.budget,
        r2.[month]
FROM results r
LEFT JOIN results r1
    ON r.id = r1.id and r.rn = r1.rn-1
LEFT JOIN results r2
    ON r1.id = r2.id and r1.rn = r2.rn-1
ORDER BY r.rn

Output:

id  element budget  month   element budget  month       element budget  month
1   CC      100     january FF      100     february    DD      200     march
2   SS      150     june    NULL    NULL    NULL        NULL    NULL    NULL

EDIT:

If you want to create view you can use this query:

CREATE VIEW ViewNameHere
AS
    WITH months AS ( -- Table with months for ordering
    SELECT DATENAME(month,'1970-01-01') as [month], 1 as [level]
    UNION ALL
    SELECT DATENAME(month,DATEADD(month,[level],'1970-01-01')), [Level]+1
    FROM months
    WHERE [level] < 12
    )
    , results AS ( --Temp cte with add of ROW_NUMBERS()
    SELECT  id, 
            element, 
            budget, 
            o.[month],
            ROW_NUMBER() OVER (PARTITION BY Id ORDER BY m.[level]) as rn
    FROM OPMdata o
    LEFT JOIN months m
        ON m.[month] = o.[month]
    )
    --Final query:
    --every column with unique name 'value*'
    SELECT TOP 1 WITH TIES 
            r.id        as value1,
            r.element   as value2,
            r.budget    as value3,
            r.[month]   as value4,
            r1.element  as value5,
            r1.budget   as value6,
            r1.[month]  as value7,
            r2.element  as value8,
            r2.budget   as value9,
            r2.[month]  as value10,
    FROM results r
    LEFT JOIN results r1
        ON r.id = r1.id and r.rn = r1.rn-1
    LEFT JOIN results r2
        ON r1.id = r2.id and r1.rn = r2.rn-1
    ORDER BY r.rn