whywake
Reputation: 910
Pivot multiple rows in SQL Sever
I have data like below:
CREATE TABLE #Temp
(RuleName VARCHAR(100),
ParamValue VARCHAR(100),
ParamName VARCHAR(100))
INSERT INTO #Temp
VALUES ('BroadBandAviva','BroadBandAviva1','Rule Name'),
('BroadBandAviva','BroadBand','Expense Category name'),
('BroadBandAviva','DSF','org unit name'),
('BroadBandAviva','ASSISTANT SALES MANAGER','designation'),
('BroadBandAviva','Category A','cityclassification'),
('BroadBandAviva','2000','Eligible Amount'),
('BroadBandAviva','BroadBandAviva2','Rule Name'),
('BroadBandAviva','BroadBand','Expense Category name'),
('BroadBandAviva','DSF','org unit name'),
('BroadBandAviva','ASSISTANT SALES MANAGER','designation'),
('BroadBandAviva','ROI-Rest of India','cityclassification'),
('BroadBandAviva','2000','Eligible Amount')
and I want the output like this:
RuleName Col1 Col2 Col3 Col4 Col5 Col6
---------------------------------------------------------------------------------
BroadBandAviva Rule Name Expense Category name org unit name designation cityclassification Eligible Amount
BroadBandAviva BroadBandAviva1 BroadBand DSF ASSISTANT SALES MANAGER Category A 2000
BroadBandAviva BroadBandAviva2 BroadBand DSF ASSISTANT SALES MANAGER ROI-Rest of India 2000
Basically the first column should stay as it is, third column should become the first row and the second column should come in second row onwards. I tried with pivot function but I could not get the desired output.
Upvotes: 0
Views: 108
Answers (2)
Paul Maxwell
Reputation: 35563
Here is an alternative approach which places the values into separate columns, like this:
| RULENAME | GRPNO | COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |
|----------------|-------|-----------------|-----------------------|---------------|-------------------------|---------------------|-----------------|
| BroadBandAviva | -1 | Rule Name | Expense Category name | org unit name | designation | cityclassification | Eligible Amount |
| BroadBandAviva | 0 | BroadBandAviva1 | BroadBand | DSF | ASSISTANT SALES MANAGER | Category A | 2000 |
| BroadBandAviva | 1 | BroadBandAviva2 | BroadBand | DSF | ASSISTANT SALES MANAGER | ROI-Rest of India | 2000 |
Like my previous attempt I use a CTE to retain some needed calculations that are reused in a subsequent union. Unlike the previous attempt though I use conventional case expressions and group by to achieve the pivot. Here is the query.
declare @i int = (select count(distinct ParamName) from Temp)
;with cte as (
select
*
, row_number() over(order by (select 1)) as rn
, (row_number() over(order by (select 1))-1) / @i as grpno
, row_number() over(order by (select 1)) % @i as prow
from temp
)
select
rulename
, -1 as grpno
, max(case when prow = 1 then paramname end) as col1
, max(case when prow = 2 then paramname end) as col2
, max(case when prow = 3 then paramname end) as col3
, max(case when prow = 4 then paramname end) as col4
, max(case when prow = 5 then paramname end) as col5
, max(case when prow = 0 then paramname end) as col6
from cte
WHERE rn <= @i
group by
rulename
union all
select
rulename
, grpno
, max(case when prow = 1 then paramvalue end) as col1
, max(case when prow = 2 then paramvalue end) as col2
, max(case when prow = 3 then paramvalue end) as col3
, max(case when prow = 4 then paramvalue end) as col4
, max(case when prow = 5 then paramvalue end) as col5
, max(case when prow = 0 then paramvalue end) as col6
from cte
group by
rulename
, grpno
;
To follow the logic it is useful to understand the columns PROW (parameter row) and GRPNO (grouping number), here is the CTE contents showing those columns:
/*
| RULENAME | PARAMVALUE | PARAMNAME | RN | GRPNO | PROW |
|----------------|-------------------------|-----------------------|----|-------|------|
| BroadBandAviva | BroadBandAviva1 | Rule Name | 1 | 0 | 1 |
| BroadBandAviva | BroadBand | Expense Category name | 2 | 0 | 2 |
| BroadBandAviva | DSF | org unit name | 3 | 0 | 3 |
| BroadBandAviva | ASSISTANT SALES MANAGER | designation | 4 | 0 | 4 |
| BroadBandAviva | Category A | cityclassification | 5 | 0 | 5 |
| BroadBandAviva | 2000 | Eligible Amount | 6 | 0 | 0 |
| BroadBandAviva | BroadBandAviva2 | Rule Name | 7 | 1 | 1 |
| BroadBandAviva | BroadBand | Expense Category name | 8 | 1 | 2 |
| BroadBandAviva | DSF | org unit name | 9 | 1 | 3 |
| BroadBandAviva | ASSISTANT SALES MANAGER | designation | 10 | 1 | 4 |
| BroadBandAviva | ROI-Rest of India | cityclassification | 11 | 1 | 5 |
| BroadBandAviva | 2000 | Eligible Amount | 12 | 1 | 0 |
*/
Upvotes: 1
Paul Maxwell
Reputation: 35563
This is as close as I can get, maybe others can use it? note the 2000 below is in the wrong place and I cannot see why or how.
| RULENAME | PARAMS |
|----------------|----------------------------------------------------------------------------------------------------|
| BroadBandAviva | Rule Name, Expense Category name, org unit name, designation, cityclassification, Eligible Amount |
| BroadBandAviva | BroadBandAviva1, BroadBand, DSF, ASSISTANT SALES MANAGER, Category A |
| BroadBandAviva | 2000, BroadBandAviva2, BroadBand, DSF, ASSISTANT SALES MANAGER, ROI-Rest of India |
^^^^^
Here's the query I used:
declare @i int = (select count(distinct ParamName) from Temp)
;with cte as (
select
*
, row_number() over(order by (select 1)) as rn
, row_number() over(order by (select 1)) / @i as grpno
, row_number() over(order by (select 1)) % @i as prow
from temp
)
SELECT
(select RuleName from cte where rn = 1) as RULENAME
, STUFF((
SELECT
', ' + c2.ParamName
FROM CTE as c2
WHERE rn <= @i
order by c2.rn
FOR XML PATH ('')
)
, 1, 1, '') as PARAMS
--, -1, -1, -1
UNION ALL
select
cte.RuleName
, caxml.params
--, cte.grpno, prow, rn
from cte
CROSS APPLY (
SELECT
STUFF((
SELECT
', ' + c2.ParamValue
FROM CTE as c2
WHERE c2.grpno = cte.grpno
order by case when c2.prow = @i then @i else c2.prow end
FOR XML PATH ('')
)
, 1, 1, '')
) caxml (params)
where prow = 1
;
Upvotes: 1