Pivot Query on Distinct Records

PIVOT query on Distinct records

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

Transpose / Pivot distinct row attribute as column and group another attribute?

You can use the PIVOT function. If you have a known number of columns, then you can hard-code the values:

select *
from
(
  select game, player, goals
  from yourtable
) src
pivot
(
  sum(goals)
  for player in ([John], [Paul], [Mark], [Luke])
) piv
order by game

See SQL Fiddle with Demo

If you have an unknown number of columns, then you can use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(player) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT game, ' + @cols + ' from 
             (
                select game, player, goals
                from yourtable
            ) x
            pivot 
            (
                sum(goals)
                for player in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

SQL Server Pivot - distinct values

You are very close, you would only have to add the pivoted fields to your SELECT list (and remove that DISTINCT, because the data is grouped automatically):

SELECT [Day], [Coke], [Pepsi], [Tango]
FROM [TABLE]
PIVOT (SUM([Cost]) FOR [Prod] IN ([Coke], [Pepsi], [Tango])) AS PIVOTSALES

But...

I hope that your table also has a primary key, and if so, this query will not work, since PIVOT not only creates columns for the values of the pivoted field (Prod) that will contain values calculated using the value field (Cost) but also does a GROUP BY by all other columns contained in what is specified in the FROM clause. Therefore, if your table contains more than the mentionned 3 columns Day, Prod and Cost, you will have to specify a subquery in the FROM clause (and give it an alias):

SELECT [Day], [Coke], [Pepsi], [Tango]
FROM (SELECT [Day], [Cost], [Prod] FROM [TABLE]) AS [Alias]
PIVOT (SUM([Cost]) FOR [Prod] IN ([Coke], [Pepsi], [Tango])) AS PIVOTSALES

Select distinct values across columns

One option is to UNPIVOT your data and then PIVOT via a dense_rank()

Example

Declare @YourTable Table ([ID] varchar(50),[Color1] varchar(50),[Color2] varchar(50),[Color3] varchar(50),[Color4] varchar(50))
Insert Into @YourTable Values 
  (1,'Red','Blue','Blue','Green')

Select *
 From  (
        Select A.ID
              ,B.Item
              ,Col = concat('Color',dense_rank() over (partition by ID order by Item) )
         from @YourTable A
         Cross Apply ( values (color1)
                             ,(color2)
                             ,(color3)
                             ,(color4)
                     ) B(Item)
         Where ID=1
       ) src
 Pivot (max(item) for Col in (Color1,Color2,Color3,Color4) ) pvt

Returns

ID  Color1  Color2  Color3  Color4
1   Blue    Green   Red     NULL

Note: The Where ID=1 is optional

SQL Server : how to pivot where new columns have multiple unique values

Really you want an UNPIVOT to take care of the multiple columns, then a PIVOT to get your data. My favorite way of doing an UNPIVOT is to use CROSS APPLY, but you can do it however you want. Like such.

SELECT E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt

And of course if you want to see what column the values are coming from:

SELECT ColumnName, E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt

EDIT:

Here is a solution for multiple types. Basically you have to have do multiple queries, one for each data type. Among other things you have a SUM in your numeric query that won't work on varchar columns. Your output also has to be all the same. Meaning you have to convert any numeric or date columns into varchar.

SELECT ColumnName, CAST(E AS varchar(30)) E, CAST(F AS varchar(30)) F, CAST(G AS varchar(30)) G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('PRODUCT', PRODUCT),
                        ('QTY', QTY),
                        ('SMALLEST',SMALLEST)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (SUM(VALUE) FOR CUST IN (E,F,G)) AS pvt
UNION ALL
SELECT ColumnName, E, F, G
FROM (
SELECT CUST, ColumnName, Value
FROM Product
CROSS APPLY ( VALUES ('CharColA', CharColA),
                        ('CharColB', CharColB)) 
    UnPivoted(ColumnName, Value)) up
PIVOT (MIN(VALUE) FOR CUST IN (E,F,G)) AS pvt

count and display distinct values in excel rows

You could do this by using the Data Model and creating measures to display text values within the Value field of a PivotTable, but I think Power Query would be more efficient.

• Load your data into Power Query. Data tab in the Ribbon > From Table/Range

• Select the Name column, right-click, Unpivot Other Columns

• Select the Name and Value columns, right-click, Group By.

• New Column name = "Count", Operation = "Count Rows", Column will be blank.

• Select the Value and Count columns. Go to the Transform tab in the ribbon, select Merge Columns under the Text Column section. Use whatever seperator you want, I chose colon.

• Right-click the Name column, select Group By. Name the column whatever you like, and do a Sum operation on your newly Merged column.

Your resulting column will have an error. In the formula bar replace this code:

= Table.Group(#"Merged Columns", {"name"}, {{"MergedRows", each List.Sum([Merged]), type text}})

With this code, replacing MergedRows with your column header.

= Table.Group(#"Merged Columns", {"name"}, {{"MergedRows", each Text.Combine([Merged],":"), type text}})

• Notice how my Text.Combine separator is the same colon as I used earlier to merge columns. Now you can selected your fully merged column, and choose Split Column under the Transform tab in the ribbon. Choose delimiter and colon.

• Now you just need to clean up the column order and headers.

---

Related Topics