SQL Server Pivot – Convert Rows into Columns and Group Columns

pivotsql server

I saw a lot of example on how to convert rows into columns and I was able to accomplish that.Here is the table that have:

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey 
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

This what I hope the table would look like: As you can see some open open the email 2 times. Instead of having 2 rows like in the first table this one add the actions taken by the person by subject

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date], 
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId

Best Answer

Thankfully SQL Server has a built in function called a PIVOT that should do exactly what you are looking for. There is a great article at SQLServerTutorial.Net which has some great examples on how to use this.

I think that this query should give you what you are looking for. I built a table variable called @Table for the purpose of my testing.:

SELECT UserID,
[Date],
[O] AS 'O(Open)', --The value in the [Action] Column called out below, becomes the column name up here
[C] AS 'C(Click)', --The value in the [Action] Column called out below, becomes the column name up here
[P] AS 'P(Purchase)', --The value in the [Action] Column called out below, becomes the column name up here
Tracking,
Zip,
[Subject]
FROM
(
    SELECT UserID,
    [Action],
    [Date],
    Tracking,
    Zip,
    [Subject]
    FROM @Table
) T
PIVOT
(
    COUNT([Action])
    FOR [Action] IN([O], [C], [P]) --Call out the values in the Action Column
) AS Pivot_Table

For Me It Produced a Table Like Below:

UserID  Date        O(Open) C(Click)    P(Purchase) Tracking    Zip     Subject
cbg10   2020-01-02  2       1           1           Amz         30105   $1 Sales journey
cbg10   2020-04-18  1       1           1           TG          30105   Summer arrives
kel64   2020-01-02  3       2           1           Amz         45685   $1 Sales journey

I can break this down in a little more details if you need me to, since there are several nuances in what you asked for, grouping by multiple columns, mostly.

Hope this helps!

Related Solutions

Mysql – How to convert rows into columns and query on them in Mysql

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
); 
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES 
  (1, '23', 1), 
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4), 
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;

SSAS Multidimensional Drillthrough – Handling Many-to-Many Relationships

If your drillthrough command includes a dimension attribute from a many-to-many dimension then it behaves as described: showing a row zero to many times.

The easiest solution is to create a drillthrough action and mark that action as the default drillthrough action and specify the columns you want to include in the drillthrough results. If you don't include the many-to-many dimensions then it will stop skipping or duplicating rows.

Best Answer

Related Solutions

Mysql – How to convert rows into columns and query on them in Mysql

SSAS Multidimensional Drillthrough – Handling Many-to-Many Relationships

Related Question