Here is my sample table
---+-------------+------------------+------------------+------------------+--------------------+--------------------+--------------------+
| Id| CompanyName|part1_sales_amount|part2_sales_amount|part3_sales_amount|part1_sales_quantity|part2_sales_quantity|part3_sales_quantity|
+---+-------------+------------------+------------------+------------------+--------------------+--------------------+--------------------+
| 1| FastCarsCo| 1| 2| 3| 4| 5| 6|
| 2|TastyCakeShop| 4| 5| 6| 4| 5| 6|
| 3| KidsToys| 7| 8| 9| 7| 8| 9|
| 4| FruitStall| 10| 11| 12| 10| 11| 12|
+---+-------------+------------------+------------------+------------------+--------------------+--------------------+--------------------+
Here is output table that i want
+---+-------------+------------------+------------------+------------------+
| Id| CompanyName|Account |amount |quantity |
+---+-------------+------------------+------------------+------------------+
| 1| FastCarsCo| part1_sales| 1| 1|
| 1| FastCarsCo| part2_sales| 2| 2|
| 1| FastCarsCo| part3_sales| 3| 3|
| 2|TastyCakeShop| part1_sales| 4| 4|
| 2|TastyCakeShop| part2_sales| 5| 5|
| 2|TastyCakeShop| part3_sales| 6| 6|
| 3| KidsToys| part1_sales| 7| 7|
| 3| KidsToys| part2_sales| 8| 8|
| 3| KidsToys| part3_sales| 9| 9|
| 4| FruitStall| part1_sales| 10| 10|
| 4| FruitStall| part2_sales| 11| 11|
| 4| FruitStall| part3_sales| 12| 12|
+---+-------------+------------------+------------------+------------------+
Things I already did
SELECT
Id,
CompanyName,
REPLACE ( acc , '_amount' , '' ) AS Account,
amount,
quantity
FROM
(
SELECT Id, CompanyName, part1_sales_amount ,part2_sales_amount ,part3_sales_amount ,part1_sales_quantity ,part2_sales_quantity ,part3_sales_quantity
FROM privot
) src
UNPIVOT
(
amount FOR acc IN (part1_sales_amount ,part2_sales_amount ,part3_sales_amount )
) pvt1
UNPIVOT
(
quantity FOR acc1 IN (part1_sales_quantity, part2_sales_quantity, part3_sales_quantity )
) pvt2
It gave some result but it seems like there is some unexpected record also(Like cross join). so my final step the WHERE clause, what should I write in WHERE clause.I tried many thing but non is a correct one.
Note: In my real data base here are almost 200 column like those part1_sales_amount and part1_sales_quantity
Please any help appreciate.
Single unpivot and choose the corresponding quantity column:
declare @privot table
(
id int,
CompanyName varchar(20),
part1_sales_amount money,
part2_sales_amount money,
part3_sales_amount money,
part4_sales_amount money,
part5_sales_amount money,
part1_sales_quantity int,
part2_sales_quantity int,
part3_sales_quantity int,
part4_sales_quantity int,
part5_sales_quantity int
);
insert into @privot
(
Id, CompanyName,
part1_sales_amount, part2_sales_amount, part3_sales_amount, part4_sales_amount, part5_sales_amount,
part1_sales_quantity, part2_sales_quantity, part3_sales_quantity, part4_sales_quantity, part5_sales_quantity
)
values
(1, 'FastCarsCo', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
(2, 'TastyCakeShop', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100),
(3, 'KidsToys', 11, 21, 31, 41, 51, 61, 71, 81, 91, 101),
(4, 'FruitStall', 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000);
select
Id, CompanyName, replace(acc, '_amount', '') as acc, amount,
quantity=choose(/*try_cast ??*/replace(left(acc, charindex('_', acc)-1), 'part', ''), /*quantity columns*/part1_sales_quantity, part2_sales_quantity, part3_sales_quantity, part4_sales_quantity, part5_sales_quantity)
FROM
(
SELECT *
--Id, CompanyName,
--part1_sales_amount, part2_sales_amount, part3_sales_amount, part4_sales_amount, part5_sales_amount,
--part1_sales_quantity ,part2_sales_quantity ,part3_sales_quantity , part4_sales_quantity, part5_sales_quantity
FROM @privot
) src
UNPIVOT
(
amount FOR acc IN (/*amount columns*/part1_sales_amount ,part2_sales_amount ,part3_sales_amount, part4_sales_amount, part5_sales_amount )
) pvt1;