April 18, 2019 at 10:11 am
I need to return data in a table format, that has a variable number of columns.
It is more easy to explain with a sample:
I have 3 tables: 1 with sales items, 1 with price list names, and 1 with prices, and I need to generate a price list of all sales items with all prices. This means the number of columns is variable as we can have multiple price lists. I think I have to use then PIVOT command, but have never used this and I do not know where to start. Can someone help me out with this?
declare @items table (id int identity(1,1) primary key, name varchar(255))
insert into @items (name) values ('Item 1'),('Item 2'),('Item 3')
declare @pricelists table (id int identity(1,1) primary key, name varchar(255))
insert into @pricelists (name) values ('Price list A'),('Price list B')
declare @prices table (id int identity(1,1) primary key, item int, pricelist int, price decimal(19,2))
insert into @prices (item, pricelist, price) values (1, 1, 5.00),(2, 1, 10),(2, 2, 12)
The output should look like this:
---------------------------------------------
--| ITEM NAME | PRICE LIST A | PRICE LIST B |
---------------------------------------------
--| Item 1 | 5.00 | 0 |
--| Item 2 | 10 | 12 |
--| Item 3 | 0 | 0 |
April 18, 2019 at 11:23 am
The normal (non-dynamic) way you would achieve this would be with a something called a Cross-Pivot. Like the below (note I have changed the objects to persisted objects, due to the dynamic solution coming):
USE Sandbox;
GO
CREATE TABLE dbo.items (id int IDENTITY(1, 1) PRIMARY KEY,
[name] varchar(255));
INSERT INTO dbo.items ([name])
VALUES ('Item 1'),
('Item 2'),
('Item 3');
CREATE TABLE dbo.pricelists (id int IDENTITY(1, 1) PRIMARY KEY,
[name] varchar(255));
INSERT INTO dbo.pricelists ([name])
VALUES ('Price list A'),
('Price list B');
CREATE TABLE dbo.prices (id int IDENTITY(1, 1) PRIMARY KEY,
item int,
pricelist int,
price decimal(19, 2));
INSERT INTO dbo.prices (item,
pricelist,
price)
VALUES (1, 1, 5.00),
(2, 1, 10),
(2, 2, 12);
GO
SELECT i.[name],
ISNULL(MAX(CASE pl.[name] WHEN 'Price list A' THEN p.price END),0) AS [Price list A],
ISNULL(MAX(CASE pl.[name] WHEN 'Price list B' THEN p.price END),0) AS [Price list B]
FROM dbo.items i
CROSS JOIN dbo.pricelists pl
LEFT JOIN dbo.prices p ON i.id = p.item
AND pl.id = p.pricelist
GROUP BY i.[name];
Because, however, you've stated that the number of prices vary, you have to use dynamic SQL to do so. I've used FOR XML PATH here, as I don't have access to a SQL Server 2017 Instance at the moment (you could therefore make use of STRING_AGG instead). I've also left some comments to show how you would parametrise the statement:
INSERT INTO dbo.pricelists ([name])
VALUES('Price list C');
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT i.[name],' + NCHAR(13) + NCHAR(10) +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' ISNULL(MAX(CASE pl.[name] WHEN ' + QUOTENAME(pl.[name],'''') + ' THEN p.price END),0) AS ' + QUOTENAME(pl.[name])
FROM dbo.pricelists pl
ORDER BY pl.id
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + NCHAR(13) + NCHAR(10) +
N'FROM dbo.items i' + NCHAR(13) + NCHAR(10) +
N'CROSS JOIN dbo.pricelists pl' + NCHAR(13) + NCHAR(10) +
N'LEFT JOIN dbo.prices p ON i.id = p.item' + NCHAR(13) + NCHAR(10) +
N' AND pl.id = p.pricelist' + NCHAR(13) + NCHAR(10) +
--N'WHERE i.id = @ID;' --Or something similar maybe?
N'GROUP BY i.[name];';
PRINT @SQL; --Your debuigging best friend;
EXEC sp_executesql @SQL; --N'@id int', @id = @id; --Example our how to parametrise your dynamic statement.
This will dynamically pivot your data for you and provide a column for each value in Price list. So, for the above, with the additional Price List inserted, you get the following results:
name Price list A Price list B Price list C
--------- ------------- ------------- -------------
Item 1 5.00 0.00 0.00
Item 2 10.00 12.00 0.00
Item 3 0.00 0.00 0.00
It's important you understand the above, as it's you who needs to be able to support the code and make changes to it as required (not me or any other SSC user). If you don't understand, please do ask about it.
--Cleanup
DROP TABLE dbo.items;
DROP TABLE dbo.pricelists;
DROP TABLE dbo.prices;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2019 at 12:09 pm
Another option it to dynamically create the resulting table.
Starting from a table with all the items and an empty price column, and loop through all the price lists.
In each loop you set the prices (column price), rename the column and add a new [Price] column.
It's easy to extend this with multiple columns per loop.
DECLARE @items TABLE (id int IDENTITY(1,1) PRIMARY KEY, name varchar(255));
INSERT INTO @items (name) VALUES ('Item 1'),('Item 2'),('Item 3');
DECLARE @pricelists TABLE (id int IDENTITY(1,1) PRIMARY KEY, name varchar(255));
INSERT INTO @pricelists (name) VALUES ('Price list A'),('Price list B');
DECLARE @prices TABLE (id int IDENTITY(1,1) PRIMARY KEY, item int, pricelist int, price decimal(19,2));
INSERT INTO @prices (item, pricelist, price) VALUES (1, 1, 5.00),(2, 1, 10),(2, 2, 12);
--##### Create a Temp-Table to hold the result
--##### add extra column(s) for the prices
SELECTId,
Name,
CAST(NULL AS decimal(19,2)) AS Price
INTO#PL
FROM@items;
--##### Loop for eahc PriceList
DECLARE @Id int,
@Name varchar(255);
DECLARE PL_Loop CURSOR FOR SELECT Id, Name FROM @pricelists ORDER BY name;
OPEN PL_Loop;
FETCH NEXT FROM PL_Loop
INTO@Id, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN;
--##### Set the Price in the Price-Column
UPDATE #PL
SETPrice = ISNULL(PC.price, 0)
FROM#PL PL
LEFT OUTER JOIN(SELECT * FROM @prices WHERE PriceList = @Id) PC ON PL.Id = PC.item;
--##### Rename the Column to the name of the PriceList (Unique)
EXECUTE tempdb..sp_rename @objname = '#PL.Price', @newname = @Name, @objtype = 'COLUMN';
--##### Add new Price Column for next PriceList
ALTER TABLE #PL ADD Price decimal(19,2);
FETCH NEXT FROM PL_Loop
INTO@Id, @Name;
END;
--##### Drop the column that was added after the last Pricelist
ALTER TABLE #PL DROP COLUMN Price;
--##### Return resulting table
SELECT * FROM#PL ;
Louis
April 18, 2019 at 1:32 pm
Another option it to dynamically create the resulting table. Starting from a table with all the items and an empty price column, and loop through all the price lists. In each loop you set the prices (column price), rename the column and add a new [Price] column. It's easy to extend this with multiple columns per loop.
I have to agree with Thom... doing it that way is going to be slower and a whole lot more aggravating on TempDB and its log file.
Please see the following articles.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2019 at 2:45 pm
Thanks for the sample. It does exactly what I expect.
What is it compatibility level? Does it work on SQL2012?
April 18, 2019 at 3:13 pm
Thanks for the sample. It does exactly what I expect. What is it compatibility level? Does it work on SQL2012?
Yes, the version I provided will work on all supported versions of SQL Server (2008+). I was, however, specifically using a 2012 instance to test on.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 23, 2019 at 2:46 pm
Thank you all for your help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply