I know there are lots of existing questions about pivots, but I’m struggling to understand what I’m doing wrong.
Given this data:
<code>CREATE TABLE #Charges
(
name VARCHAR(50),
Charge DECIMAL(10, 6),
MinVal DECIMAL(10, 2),
MaxVal DECIMAL(10, 2)
);
INSERT INTO #Charges (name, Charge, MinVal, MaxVal)
VALUES ('Foo', 0.500000, 50.00, 500.00),
('Foo', 1.000000, 50.00, 500.00),
('Foo', 1.500000, 50.00, 500.00),
('Foo', 1.750000, 50.00, 500.00)
</code>
<code>CREATE TABLE #Charges
(
name VARCHAR(50),
Charge DECIMAL(10, 6),
MinVal DECIMAL(10, 2),
MaxVal DECIMAL(10, 2)
);
INSERT INTO #Charges (name, Charge, MinVal, MaxVal)
VALUES ('Foo', 0.500000, 50.00, 500.00),
('Foo', 1.000000, 50.00, 500.00),
('Foo', 1.500000, 50.00, 500.00),
('Foo', 1.750000, 50.00, 500.00)
</code>
CREATE TABLE #Charges
(
name VARCHAR(50),
Charge DECIMAL(10, 6),
MinVal DECIMAL(10, 2),
MaxVal DECIMAL(10, 2)
);
INSERT INTO #Charges (name, Charge, MinVal, MaxVal)
VALUES ('Foo', 0.500000, 50.00, 500.00),
('Foo', 1.000000, 50.00, 500.00),
('Foo', 1.500000, 50.00, 500.00),
('Foo', 1.750000, 50.00, 500.00)
(in reality this is coming from a sub-query, so these values are not fixed, although name, minVal and maxVal should all be the same. There need to be ten “charge” columns in the output, although there may be less than ten in the result set I’m pivoting)
I’m trying to get this output:
<code>Foo 50.00 500.00 0.500000 1.000000 1.500000 1.750000
</code>
<code>Foo 50.00 500.00 0.500000 1.000000 1.500000 1.750000
</code>
Foo 50.00 500.00 0.500000 1.000000 1.500000 1.750000
I’ve tried to make a pivot, but I can’t even get the syntax right for the multiple columns needed as part of the FOR IN construct.
7
Assuming SQL Server 2022, you can derive the 10 columns from GENERATE_SERIES – cross apply to that and then pivot:
<code>WITH x AS
(
SELECT name, Charge, MinVal, MaxVal,
rn = ROW_NUMBER() OVER
(PARTITION BY name ORDER BY @@SPID)
FROM #Charges
)
SELECT name, MinVal, MaxVal,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM x
CROSS APPLY (SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
PIVOT (MAX(x.Charge)
FOR x.rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS p
WHERE p.n = 1;
</code>
<code>WITH x AS
(
SELECT name, Charge, MinVal, MaxVal,
rn = ROW_NUMBER() OVER
(PARTITION BY name ORDER BY @@SPID)
FROM #Charges
)
SELECT name, MinVal, MaxVal,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM x
CROSS APPLY (SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
PIVOT (MAX(x.Charge)
FOR x.rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS p
WHERE p.n = 1;
</code>
WITH x AS
(
SELECT name, Charge, MinVal, MaxVal,
rn = ROW_NUMBER() OVER
(PARTITION BY name ORDER BY @@SPID)
FROM #Charges
)
SELECT name, MinVal, MaxVal,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM x
CROSS APPLY (SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
PIVOT (MAX(x.Charge)
FOR x.rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS p
WHERE p.n = 1;
- db<>fiddle
If you’re on an older version, you can substitute…
<code>CROSS APPLY
(SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
</code>
<code>CROSS APPLY
(SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
</code>
CROSS APPLY
(SELECT value FROM GENERATE_SERIES(1,10)) AS n(n)
…with…
<code>CROSS APPLY
(SELECT TOP (10) ROW_NUMBER() OVER (ORDER BY name)
FROM sys.columns ORDER BY name) AS n(n)
</code>
<code>CROSS APPLY
(SELECT TOP (10) ROW_NUMBER() OVER (ORDER BY name)
FROM sys.columns ORDER BY name) AS n(n)
</code>
CROSS APPLY
(SELECT TOP (10) ROW_NUMBER() OVER (ORDER BY name)
FROM sys.columns ORDER BY name) AS n(n)
- db<>fiddle
In either case, this is a little tidier than conditional aggregation IMHO; however, it assumes than MinVal and MaxVal are constant for all rows of a given name.