CODEWITHSUNDEEP
sqlsql-servert-sql
Owain Esau
Owain Esau

Reputation: 1922

CTE partition in seperate columns

I am trying to get a partition of user accounts and note types, what i need is to have 1 row for each manager record (AMID) and multiple columns with a count of how many notes that user has made of each different note type.

I can get the correct results by using:

WITH CTE AS
(
    SELECT AMID, ActionTypeID, COUNT(*) AS total, ROW_NUMBER() OVER  (PARTITION BY AMID, ActionTypeID ORDER BY AMID) AS RN
    FROM personNote
    GROUP BY AMID, ActionTypeID
)
SELECT * FROM CTE

Which gives me something like this:

AMID    ActionTypeID    total   RN
1       12              231002  1
1       15              354936  1
3       0               74441   1
3       3               4       1
3       7               18      1

But this won't work in the reporting tool i need to put this code into, i need each actionType ID to be in a different column, so like this:

AMID   ActionTypeID   Total     ActionTypeID2   Total2
1      12             231002    15              354936

Thanks in advance!

Upvotes: 0

Views: 69

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

For a Dynamic Pivot with Non-Distinct/Repeating Colums

Now, this will shift or compress the number of columns. For example if AMID had only 1 ActionTypeID, the values will be displayed in 1st and 2nd columns regardless of the ActionTypeID. It would be a small matter/easier to make the columns distinct and thus "wider"

Declare @SQL varchar(max) = Stuff((Select ',' + QuoteName(concat(Col,'A')) + ',' + QuoteName(concat(Col,'T')) 
                                    From (
                                          Select Distinct Col=1000+Dense_Rank() over (Partition By AMID Order By ActionTypeID) 
                                           From  #PersonNote
                                         ) A
                                    Order by 1 
                                    For XML Path('')),1,1,'') 
Select  @SQL = '
Select [YAxis] as [AMID],' + replace(replace(@SQL,'A]','A] as [ActionTypeID]'),'T]','T] as [Total]')  + '
From (
        Select YAxis = AMID
              ,XAxis = B.XAxis
              ,Value = B.Value
         From  (
                Select AMID
                      ,ActionTypeID
                      ,XAxis = 1000+Dense_Rank() over (Partition By AMID Order By ActionTypeID)
                      ,Value=COUNT(*)
                 From  #PersonNote
                 Group By AMID, ActionTypeID
               ) A
         Cross Apply ( values (concat(A.XAxis,''A''),A.ActionTypeID)
                             ,(concat(A.XAxis,''T''),A.Value)
                     ) B (XAxis,Value)

     ) A
 Pivot (sum(Value) For [XAxis] in (' + @SQL + ') ) p'
--Print  @SQL
Exec(@SQL);

Returns

enter image description here

Using the Following Test Data

Create Table #PersonNote (AMID int,ActionTypeID int)
Insert Into #PersonNote values
(1,12),(1,12),(1,12),(1,12),(1,12),(1,12),(1,12),                -- Cnt 7
(1,15),(1,15),(1,15),(1,15),(1,15),(1,15),(1,15),(1,15),(1,15),  -- Cnt 9
(3,3),(3,3),(3,3),                                               -- Cnt 3
(3,7),(3,7),(3,7),(3,7),(3,7),(3,7),                             -- Cnt 6
(3,12)                                                           -- Cnt 1

Upvotes: 3

Related Questions