8

What is the best way to 'flatten' tables into a single row?

For example, with the following table:

+-----+-------+-------------+------------------+
| Id  | hProp | iDayOfMonth | dblTargetPercent |
+-----+-------+-------------+------------------+
| 117 |    10 |           5 |           0.1400 |
| 118 |    10 |          10 |           0.0500 |
| 119 |    10 |          15 |           0.0100 |
| 120 |    10 |          20 |           0.0100 |
+-----+-------+-------------+------------------+

I would like to produce the following table:

+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
| hProp | iDateTarget1 | dblPercentTarget1 | iDateTarget2 | dblPercentTarget2 | iDateTarget3 | dblPercentTarget3 | iDateTarget4 | dblPercentTarget4 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+
|    10 |            5 |              0.14 |           10 |              0.05 |           15 |              0.01 |           20 |              0.01 |
+-------+--------------+-------------------+--------------+-------------------+--------------+-------------------+--------------+-------------------+

I have managed to do this using a pivot and then rejoining to the original table several times, but I'm fairly sure there is a better way. This works as expected:

select
X0.hProp,
X0.iDateTarget1,
X1.dblTargetPercent [dblPercentTarget1],
X0.iDateTarget2,
X2.dblTargetPercent [dblPercentTarget2],
X0.iDateTarget3,
X3.dblTargetPercent [dblPercentTarget3],
X0.iDateTarget4,
X4.dblTargetPercent [dblPercentTarget4]
from (
    select
        hProp,
        max([1]) [iDateTarget1],
        max([2]) [iDateTarget2],
        max([3]) [iDateTarget3],
        max([4]) [iDateTarget4]
    from (
        select
            *,
            rank() over (partition by hProp order by iWeek) rank#
        from [Table X]
    ) T
    pivot (max(iWeek) for rank# in ([1],[2],[3], [4])) pv
    group by hProp
) X0
left join [Table X] X1 on X1.hprop = X0.hProp and X1.iWeek = X0.iDateTarget1
left join [Table X] X2 on X2.hprop = X0.hProp and X2.iWeek = X0.iDateTarget2
left join [Table X] X3 on X3.hprop = X0.hProp and X3.iWeek = X0.iDateTarget3
left join [Table X] X4 on X4.hprop = X0.hProp and X4.iWeek = X0.iDateTarget4
12

Here is one way of getting the result set you want without doing the multiple joins. It takes a little more setup and uses two pivot operations instead of one, but avoids the multiple joins.

I admit that I had to look it up, but Ken O'Bonn had a great article. https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/

/** Build up a Table to work with. **/
DECLARE @T TABLE
    (
    ID INT NOT NULL PRIMARY KEY
    , hProp INT NOT NULL
    , iDayOfMonth INT NOT NULL
    , dblTargetPercent DECIMAL(6,4) NOT NULL
    )

INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
        , (118, 10, 10, 0.0500) 
        , (119, 10, 15, 0.0100)
        , (120, 10, 20, 0.0100)

/** Create a CTE and give us predictable names to work with for
    date and percentage
    **/
;WITH CTE_Rank AS
    (
    SELECT ID
        , hProp
        , iDayOfMonth 
        , dblTargetPercent 
        , sDateName = 'iDateTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
        , sPercentName = 'dblPercentTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
    FROM @T
    )
SELECT hProp 
    , iDateTarget1 = MAX(iDateTarget1)
    , dblPercentTarget1 = MAX(dblPercentTarget1)
    , iDateTarget2 = MAX(iDateTarget2)
    , dblPercentTarget2 = MAX(dblPercentTarget2)
    , iDateTarget3 = MAX(iDateTarget3)
    , dblPercentTarget3 = MAX(dblPercentTarget3)
    , iDateTarget4 = MAX(iDateTarget4)
    , dblPercentTarget4 = MAX(dblPercentTarget4)
FROM CTE_Rank AS R
    PIVOT(MAX(iDayOfMonth) FOR sDateName IN ([iDateTarget1], [iDateTarget2], [iDateTarget3], [iDateTarget4])) AS DayOfMonthName 
    PIVOT(MAX(dblTargetPercent) FOR sPercentName IN (dblPercentTarget1, dblPercentTarget2, dblPercentTarget3, dblPercentTarget4)) AS TargetPercentName
GROUP BY hProp
| improve this answer | |
  • Thanks. I'd say this looks a little harder to read than what I did with multiple joins on the same table. which way would you choose? i imagine that the multiple joins would not be too expensive due to the query optimizer? – Zach Smith Dec 11 '17 at 12:24
  • 1
    Well, for performance, the two PIVOT's require only a single scan over the table, where as the multiple joins require at minimum multiple seeks. Absolute performance difference will depend heavily on how many rows are in the table and what the indexes look like. As for how easy it is to read, I think it's fairly straightforward once you see how it's doing what it's doing. But if you are using this for a report of some sort, I would look into making the presentation layer do the pivoting. For example, in SSRS I would just feed it the raw query and use a matrix. – Jonathan Fite Dec 11 '17 at 14:10
  • I'm sending this to a browser. Pivoting in sql server makes the Javascript simpler in this case – Zach Smith Dec 12 '17 at 5:54
  • The table will always be small. It's configuration for a report builder – Zach Smith Dec 12 '17 at 5:55
8

Given:

DECLARE @T table
(
    ID integer NOT NULL PRIMARY KEY,
    hProp integer NOT NULL,
    iDayOfMonth integer NOT NULL,
    dblTargetPercent decimal(6,4) NOT NULL
);

INSERT @T
    (ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES 
    (117, 10, 05, 0.1400),
    (118, 10, 10, 0.0500),
    (119, 10, 15, 0.0100),
    (120, 10, 20, 0.0100);

You can get the result described with a manual pivot:

WITH Ranked AS
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
            PARTITION BY T.hProp 
            ORDER BY T.iDayOfMonth)
    FROM @T AS T
)
SELECT
    R.hProp,
    iDateTarget1 =      MAX(CASE WHEN R.rn = 1 THEN R.iDayOfMonth END),
    dblPercentTarget1 = MAX(CASE WHEN R.rn = 1 THEN R.dblTargetPercent END),
    iDateTarget2 =      MAX(CASE WHEN R.rn = 2 THEN R.iDayOfMonth END),
    dblPercentTarget1 = MAX(CASE WHEN R.rn = 2 THEN R.dblTargetPercent END),
    iDateTarget3 =      MAX(CASE WHEN R.rn = 3 THEN R.iDayOfMonth END),
    dblPercentTarget3 = MAX(CASE WHEN R.rn = 3 THEN R.dblTargetPercent END),
    iDateTarget4 =      MAX(CASE WHEN R.rn = 4 THEN R.iDayOfMonth END),
    dblPercentTarget4 = MAX(CASE WHEN R.rn = 4 THEN R.dblTargetPercent END)
FROM Ranked AS R
GROUP BY
    R.hProp;

db<>fiddle here

| improve this answer | |
2

I prefer to unpivot using cross apply then use a single pivot. There is an issue with this technique as the two values will end up being mapped to the same column(type) so that needs to be handled on the way out to recast to the proper type. However in my experience this method performs very well with bigger data sets. Notice there is no group by.

Using the same source data:

;with src (hProp, iDayOfMonth, dblTargetPercent, rw) as (
select hProp, iDayOfMonth, dblTargetPercent, 
    ROW_NUMBER() over (partition by hProp order by iDayOfMonth) 
from @T
)
,unpvt(hProp, typ, val) as (
select hprop, ca.typ + ltrim(rw), ca.val from src
cross apply (values (iDayOfMonth, 'iDayOfMonth'),(dblTargetPercent, 'dblTargetPercent')) ca (val, typ)
)

select *
from unpvt
pivot (max(val) for typ in ([iDayOfMonth1],[dblTargetPercent1],[iDayOfMonth2],[dblTargetPercent2],
    [iDayOfMonth3],[dblTargetPercent3],[iDayOfMonth4],[dblTargetPercent4]))p

I tested my solution with 4 million rows using the follow code to generate test data:

if object_id(N'tempdb..#T',N'U') is not null drop table #T
create table #T(ID int identity(1,1) primary key clustered,
    hProp int not null, iDayOfMonth int not null, dblTargetPercent decimal(6,4) not null)

;with src(hProp) as (
select 1 union all 
select hProp+1 from src where hProp+1 <= 1000000
)
,dta(iDayOfMonth, dblTargetPercent) as (
select 5,  0.1400 union all 
select 10, 0.0500 union all 
select 15, 0.0100 union all 
select 20, 0.0100
)
insert #T(hProp, iDayOfMonth, dblTargetPercent)
select hProp, iDayOfMonth, dblTargetPercent
from src, dta
option(maxrecursion 0)

Paul White solution is fastest followed by mine. Old school wins!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.