Mixing
PIVOT query on Distinct records
up vote
5
down vote
favorite
I have below table:
------------------------------------------------------
| Id Code percentage name name1 activity |
-----------------------------------------------------
| 1 Prashant 43.43 James James_ Running |
| 1 Prashant 70.43 Sam Sam_ Cooking |
| 1 Prashant 90.34 Lisa Lisa_ Walking |
| 1 Prashant 0.00 James James_ Stealing |
| 1 Prashant 0.00 James James_ Lacking |
| 1 Prashant 73 Sam Sam_ Cooking 1 |
------------------------------------------------------
1) The problem is, due to MAX function it is ignoring 0.00 value with Column name Name1 of Lacking
2) Expected Result:
-------------------------------------------------------------------
Id Code James James_ Sam Sam_ Lisa Lisa_
-------------------------------------------------------------------
1 Prashant Running 43.43 Cooking 3.43 Walking 90.34
1 Prashant Stealing 0.0 Cooking 1 73 NULL NULL
1 Prashant Lacking 0.0 NULL NULL NULL NULL
-------------------------------------------------------------------
The PIVOT Query what I tried:
DECLARE @DynamicPivotQuery NVARCHAR(MAX)
SET @DynamicPivotQuery = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
INTO ##TempPivot
FROM A
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'
EXECUTE(@DynamicPivotQuery)
SELECT *
INTO #RESULT
FROM ##TempPivot
SELECT *
FROM #RESULT
Sample query to generate data:
CREATE TABLE A
(
Id NVARCHAR(10),
Code NVARCHAR(MAX),
percentage NVARCHAR(MAX),
name NVARCHAR(MAX),
name1 NVARCHAR(MAX),
activity NVARCHAR(MAX)
)
INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')
sql-server pivot asked 2 days ago
Prashant Pimpale
1,7852622
add a comment |
up vote
5
down vote
favorite
I have below table:
------------------------------------------------------
| Id Code percentage name name1 activity |
-----------------------------------------------------
| 1 Prashant 43.43 James James_ Running |
| 1 Prashant 70.43 Sam Sam_ Cooking |
| 1 Prashant 90.34 Lisa Lisa_ Walking |
| 1 Prashant 0.00 James James_ Stealing |
| 1 Prashant 0.00 James James_ Lacking |
| 1 Prashant 73 Sam Sam_ Cooking 1 |
------------------------------------------------------
1) The problem is, due to MAX function it is ignoring 0.00 value with Column name Name1 of Lacking
2) Expected Result:
-------------------------------------------------------------------
Id Code James James_ Sam Sam_ Lisa Lisa_
-------------------------------------------------------------------
1 Prashant Running 43.43 Cooking 3.43 Walking 90.34
1 Prashant Stealing 0.0 Cooking 1 73 NULL NULL
1 Prashant Lacking 0.0 NULL NULL NULL NULL
-------------------------------------------------------------------
The PIVOT Query what I tried:
DECLARE @DynamicPivotQuery NVARCHAR(MAX)
SET @DynamicPivotQuery = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
INTO ##TempPivot
FROM A
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'
EXECUTE(@DynamicPivotQuery)
SELECT *
INTO #RESULT
FROM ##TempPivot
SELECT *
FROM #RESULT
Sample query to generate data:
CREATE TABLE A
(
Id NVARCHAR(10),
Code NVARCHAR(MAX),
percentage NVARCHAR(MAX),
name NVARCHAR(MAX),
name1 NVARCHAR(MAX),
activity NVARCHAR(MAX)
)
INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')
asked 2 days ago
Prashant Pimpale
1,7852622
Do you have to use a temp table to get your results?
– Birel
2 days ago
Sorry, didn't get you!
– Prashant Pimpale
yesterday
add a comment |
up vote
5
down vote
favorite
up vote
5
down vote
favorite
I have below table:
------------------------------------------------------
| Id Code percentage name name1 activity |
-----------------------------------------------------
| 1 Prashant 43.43 James James_ Running |
| 1 Prashant 70.43 Sam Sam_ Cooking |
| 1 Prashant 90.34 Lisa Lisa_ Walking |
| 1 Prashant 0.00 James James_ Stealing |
| 1 Prashant 0.00 James James_ Lacking |
| 1 Prashant 73 Sam Sam_ Cooking 1 |
------------------------------------------------------
1) The problem is, due to MAX function it is ignoring 0.00 value with Column name Name1 of Lacking
2) Expected Result:
-------------------------------------------------------------------
Id Code James James_ Sam Sam_ Lisa Lisa_
-------------------------------------------------------------------
1 Prashant Running 43.43 Cooking 3.43 Walking 90.34
1 Prashant Stealing 0.0 Cooking 1 73 NULL NULL
1 Prashant Lacking 0.0 NULL NULL NULL NULL
-------------------------------------------------------------------
The PIVOT Query what I tried:
DECLARE @DynamicPivotQuery NVARCHAR(MAX)
SET @DynamicPivotQuery = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
INTO ##TempPivot
FROM A
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'
EXECUTE(@DynamicPivotQuery)
SELECT *
INTO #RESULT
FROM ##TempPivot
SELECT *
FROM #RESULT
Sample query to generate data:
CREATE TABLE A
(
Id NVARCHAR(10),
Code NVARCHAR(MAX),
percentage NVARCHAR(MAX),
name NVARCHAR(MAX),
name1 NVARCHAR(MAX),
activity NVARCHAR(MAX)
)
INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')
asked 2 days ago
Prashant Pimpale
1,7852622
I have below table:
------------------------------------------------------
| Id Code percentage name name1 activity |
-----------------------------------------------------
| 1 Prashant 43.43 James James_ Running |
| 1 Prashant 70.43 Sam Sam_ Cooking |
| 1 Prashant 90.34 Lisa Lisa_ Walking |
| 1 Prashant 0.00 James James_ Stealing |
| 1 Prashant 0.00 James James_ Lacking |
| 1 Prashant 73 Sam Sam_ Cooking 1 |
------------------------------------------------------
1) The problem is, due to MAX function it is ignoring 0.00 value with Column name Name1 of Lacking
2) Expected Result:
-------------------------------------------------------------------
Id Code James James_ Sam Sam_ Lisa Lisa_
-------------------------------------------------------------------
1 Prashant Running 43.43 Cooking 3.43 Walking 90.34
1 Prashant Stealing 0.0 Cooking 1 73 NULL NULL
1 Prashant Lacking 0.0 NULL NULL NULL NULL
-------------------------------------------------------------------
The PIVOT Query what I tried:
DECLARE @DynamicPivotQuery NVARCHAR(MAX)
SET @DynamicPivotQuery = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
INTO ##TempPivot
FROM A
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'
EXECUTE(@DynamicPivotQuery)
SELECT *
INTO #RESULT
FROM ##TempPivot
SELECT *
FROM #RESULT
Sample query to generate data:
CREATE TABLE A
(
Id NVARCHAR(10),
Code NVARCHAR(MAX),
percentage NVARCHAR(MAX),
name NVARCHAR(MAX),
name1 NVARCHAR(MAX),
activity NVARCHAR(MAX)
)
INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')
asked 2 days ago
Prashant Pimpale
1,7852622
asked 2 days ago
Prashant Pimpale
1,7852622
edited yesterday
asked 2 days ago
Prashant Pimpale
1,7852622
asked 2 days ago
Prashant Pimpale
1,7852622
asked 2 days ago
Prashant Pimpale
1,7852622
1,7852622
Do you have to use a temp table to get your results?
– Birel
2 days ago
Sorry, didn't get you!
– Prashant Pimpale
yesterday
add a comment |
Do you have to use a temp table to get your results?
– Birel
2 days ago
Sorry, didn't get you!
– Prashant Pimpale
yesterday
Do you have to use a temp table to get your results?
– Birel
2 days ago
Do you have to use a temp table to get your results?
– Birel
2 days ago
Sorry, didn't get you!
– Prashant Pimpale
yesterday
Sorry, didn't get you!
– Prashant Pimpale
yesterday
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
The idea is, in thefirst common table expression, you transform the A table to this:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql
answered yesterday
Max Szczurek
2,9981122
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
The idea is, in thefirst common table expression, you transform the A table to this:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql
answered yesterday
Max Szczurek
2,9981122
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
add a comment |
up vote
1
down vote
accepted
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
The idea is, in thefirst common table expression, you transform the A table to this:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql
answered yesterday
Max Szczurek
2,9981122
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
The idea is, in thefirst common table expression, you transform the A table to this:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql
answered yesterday
Max Szczurek
2,9981122
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
The idea is, in thefirst common table expression, you transform the A table to this:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql
answered yesterday
Max Szczurek
2,9981122
edited 10 hours ago
answered yesterday
Max Szczurek
2,9981122
answered yesterday
Max Szczurek
2,9981122
answered yesterday
Max Szczurek
2,9981122
2,9981122
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
add a comment |
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
Thanks...will try first in the tom morning
– Prashant Pimpale
yesterday
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
worked like a charm, what about the dynamic query? That means I am using Dynamic Query and columns to pivot
– Prashant Pimpale
11 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
Amazing... That is what I have tried!
– Prashant Pimpale
9 hours ago
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372997%2fpivot-query-on-distinct-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you have to use a temp table to get your results?
– Birel
2 days ago
Sorry, didn't get you!
– Prashant Pimpale
yesterday