Converting Columns to Rows based on specific column values
-
Hi,
SQL 2012
I have some sample data from a query where multiple charges may be applied to a specific product (haulage costs, inspection etc). I need to output the data to produce a row for each product and/by charge where the charge is <> 0.
For example
If a current Sales row has 2 charges (both <>0) then I need to convert that single row into 2 rows.
Existing data sample:
CREATE TABLE #Results
(
Location[nvarchar](10),
Customer[nvarchar](10),
Product[nvarchar](20),
SalesOrderNumber[nvarchar](20),
PriceNumeric(38, 2),
CreatedDateDateTime,
ChargeName[nvarchar](20),
ChargeAmountnumeric(20,2),
Charge1numeric(20,2),
Charge2numeric(20,2),
Charge3numeric(20,2),
Charge4numeric(20,2),
Charge5numeric(20,2)
);
INSERT #Results
VALUES
('London', 'Cus1', 'Prod1', '1234', '45.50', '2022-07-05 00:00:00.000', Null, Null, '0.00', '2.54', '4.43', '0.00', '0.00'),
('London', 'Cus1', 'Prod2', '5678', '32.90', '2022-07-05 00:00:00.000', Null, Null, '0.00', '8.32', '0.00', '0.00', '0.00'),
('New York', 'Cus4', 'Prod7', '4545', '26.90', '2022-07-05 00:00:00.000', Null, Null, '1.00', '0.00', '0.00', '0.00', '7.05')Current output:
Required output:
Also, there isn't a Primary Key in my real query, as rows may contain repeating data in the non charge columns (ie. 'Location' to 'CreatedDate' columns).
Any ideas?
Thanks in advance.
-
Okay, I totally stole this from an article that Kenneth Fisher wrote (@sqlstudies144). Basically, you use CROSS APPLY to "stack" the repeating groups, and then you query that. Here's my query:
SELECT [Location],
Customer,
Product,
SalesOrderNumber,
Price,
CreatedDate,
CrossApplied.ChargeName,
CrossApplied.ChargeAmount
FROM Results
CROSS APPLY (VALUES ('Charge1',Charge1)
,('Charge2',Charge2),
('Charge3',Charge3),
('Charge4',Charge4),
('Charge5',Charge5)) CrossApplied(ChargeName, ChargeAmount)
WHERE CrossApplied.ChargeAmount>0;Note the columns in the VALUES() group. The first I called "ChargeName", and the second "ChargeAmount", and that virtual table created by the CROSS APPLY I ever so cleverly called "CrossApplied" to make it obvious where the funky data was coming from. (You can name it anything you want... Originally I named it "ca", but I figured that wasn't terribly clear, so I fixed it.
Then I refer to the columns returned by the CROSS APPLY function by the SELECT part of the statement, and filter them.
Hope it helps!
If you have questions, feel free to ask.
Pieter
-
another option is "unpivot"
SELECT
[Location]
,Customer
,Product
,SalesOrderNumber
,Price
,CreatedDate
,ChargeName
,ChargeAmount
FROM
(
SELECT
[Location]
,Customer
,Product
,SalesOrderNumber
,Price
,CreatedDate
,Charge1
,Charge2
,Charge3
,Charge4
,Charge5
FROM
#Results
) x
UNPIVOT
(
ChargeAmount FOR ChargeName IN
(
Charge1
,Charge2
,Charge3
,Charge4
,Charge5
)
)AS unpvt;
-
Many thanks for your prompt responses. I am testing it now. Looks good.
-
To handle the scenario where additional charge columns could be added to the table, "charge6, charge7" etc, you could do the below to dynamically unpivot any columns that match your column name string
drop table if exists ##Results
go
CREATE TABLE ##Results
(
Location[nvarchar](10),
Customer[nvarchar](10),
Product[nvarchar](20),
SalesOrderNumber[nvarchar](20),
Price Numeric(38, 2),
CreatedDate DATETIME,
Charge1 numeric(20,2),
Charge2 numeric(20,2),
Charge3 numeric(20,2),
Charge4 numeric(20,2),
Charge5 numeric(20,2)
);
INSERT ##Results
VALUES
('London', 'Cus1', 'Prod1', '1234', '45.50', '2022-07-05 00:00:00.000', '0.00', '2.54', '4.43', '0.00', '0.00'),
('London', 'Cus1', 'Prod2', '5678', '32.90', '2022-07-05 00:00:00.000', '0.00', '8.32', '0.00', '0.00', '0.00'),
('New York', 'Cus4', 'Prod7', '4545', '26.90', '2022-07-05 00:00:00.000', '1.00', '0.00', '0.00', '0.00', '7.05')
DECLARE @ColsToPivot NVARCHAR(255) = ''
DECLARE @ColSearch NVARCHAR(255) = 'charge%'
SELECT @ColsToPivot = STRING_AGG(c.name,',')
FROM tempdb.sys.tables t
JOIN tempdb.sys.columns c on c.object_id = t.object_id
WHERE t.name = '##Results'
AND c.name like @ColSearch
DECLARE @SqlStatement NVARCHAR(MAX)
SET @SqlStatement = N'
SELECT
UnPivotTable.Location,
UnPivotTable.Customer,
UnPivotTable.Product,
UnPivotTable.SalesOrderNumber,
UnPivotTable.Price,
UnPivotTable.CreatedDate,
UnPivotTable.ChargeName,
UnPivotTable.ChargeValue
FROM
(
SELECT
*
FROM ##Results
) Charges
UNPIVOT
(
ChargeValue FOR Chargename IN
('+@ColsToPivot+')
) AS UnPivotTable
'
EXEC(@SqlStatement)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply