SQL Transform Crosstab Pivot Data

SQL Transform Crosstab Pivot Data

You can do a crosstab using conditional aggregation:

SELECT
    Item,
    [X] = MAX(CASE WHEN [Columns] = 'X' THEN Result END),
    [Y] = MAX(CASE WHEN [Columns] = 'Y' THEN Result END),
    [Z] = MAX(CASE WHEN [Columns] = 'Z' THEN Result END)
FROM thisTable
GROUP BY Item

Convert Access Crosstab/PIVOT query to T-SQL

IF OBJECT_ID('tmpEmployees_Test', 'U') IS NOT NULL DROP TABLE tmpEmployees_Test;
CREATE TABLE tmpEmployees_Test (EmployeeID INT, EmployeeName VARCHAR(255));
INSERT tmpEmployees_Test (EmployeeID, EmployeeName)
VALUES (1, 'Doe, Jane'), (2, 'Doe, John'), (3, 'Guy, Some');

IF OBJECT_ID('tmpOrders_Test', 'U') IS NOT NULL DROP TABLE tmpOrders_Test;
CREATE TABLE tmpOrders_Test (EmployeeID INT, Customer VARCHAR(255), Date DATE, OrderType VARCHAR(255));
INSERT tmpOrders_Test (EmployeeID, Customer, Date, OrderType)
VALUES (1, 'Fake Customer', '2016-05-14', 'New')
        , (2, 'Some Company', '2016-05-13', 'Change')
        , (3, 'Stuff Inc.', '2016-05-14', 'New')
        , (3, 'Cool Things', '2016-05-12', 'Change')
        , (3, 'Amazing Things', '2016-05-12', 'Change');

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;
-- Kept it for formatting Purpose
DECLARE @columns1 NVARCHAR(MAX)
SET @columns1 = N'';
SELECT @columns1 += N', ISNULL(p.' + QUOTENAME(Name) + ',''/'') AS ' + QUOTENAME(Name) 
  FROM (SELECT distinct CONVERT(nvarchar(30) , p.Date , 101) as Name FROM dbo.tmpOrders_Test AS p where [Date] > GETDATE()-7
  ) AS x;

SET @sql = N'
SELECT EmployeeName, Count(*) as Total  ' +  @columns1  + '
FROM
(
    SELECT  EmployeeID, EmployeeName' + ''+ @columns1 + '' + '
    FROM
    (
      SELECT    o.employeeID,EmployeeName, CAST(COUNT(case WHEN OrderType = ''New'' then 1  end) as varchar(5)) + ''/'' + 
                CAST(COUNT(case WHEN OrderType = ''Change'' then 1  end) as varchar(5)) as OrderType, CONVERT(nvarchar(30) , p.Date , 101) as Date
       FROM     dbo.tmpOrders_Test AS p
                    INNER JOIN dbo.tmpEmployees_Test AS o
                     ON p.EmployeeID = o.EmployeeID
       GROUP BY EmployeeName, Date, o.employeeID
    ) AS j
    PIVOT
    (
      Max(OrderType) FOR Date IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p) as p JOIN tmpOrders_Test as m on p.employeeID = m.employeeID
where [Date] > GETDATE()-7
GROUP BY EmployeeName ' + @columns + '
';

PRINT @sql;
EXEC sp_executesql @sql;

This one is using dynamic Pivot. You might want to do this business logic on Application or Reporting Side instead of complex sql.

Pivot data or use crosstab - pivot one column in my table in order to get desired solution

You can use PIVOT together with IIF (similar to CASE) for arriving at result set

DECLARE @patient table(patientNumber int, physician varchar(20), Attribution varchar(30))

insert into @patient
values
(1234 ,'Jim','Consulting'  ),
(1234 ,'seth','Consulting' ),
(1234 ,'john','Referring'  ),
(2345 ,'sally','Attending' ),
(4567 ,'James','Admitting' ),
(3424 ,'jack','Admitting'  ),
(5678 ,'sally','Admitting' ),
(5678 ,'Mike','Consulting' );

SELECT patientNumber, physician, 
iif(consulting = 1, 'Y','') as consulting,
iif(Referring = 1, 'Y','') as Referring,
iif(Attending = 1, 'Y','') as Attending,
iif(Admitting = 1, 'Y','') as Admitting
FROM @patient
PIVOT
(
COUNT(Attribution) for attribution in ([Consulting],[Referring],[Attending],[Admitting])
) as pvt

One more option available using CROSS APPLY

SELECT patientNumber, physician, t.* 
FROM @patient as p
CROSS APPLY 
(
SELECT 
iif(p.Attribution = 'Consulting','Y','') AS Consulting,
iif(p.Attribution = 'Referring', 'Y','') as Referring,
iif(p.Attribution = 'Attending', 'Y','') as Attending,
iif(p.Attribution = 'Admitting', 'Y','') as Admitting
) as t

+---------------+-----------+------------+-----------+-----------+-----------+
| patientNumber | physician | consulting | Referring | Attending | Admitting |
+---------------+-----------+------------+-----------+-----------+-----------+
|          3424 | jack      |            |           |           | Y         |
|          4567 | James     |            |           |           | Y         |
|          1234 | Jim       | Y          |           |           |           |
|          1234 | john      |            | Y         |           |           |
|          5678 | Mike      | Y          |           |           |           |
|          2345 | sally     |            |           | Y         |           |
|          5678 | sally     |            |           |           | Y         |
|          1234 | seth      | Y          |           |           |           |
+---------------+-----------+------------+-----------+-----------+-----------+

How to transform Access data similar to crosstab

Not a very practical presentation of data but it is possible. Need a unique identifier field - autonumber should serve - I called it RID. Consider:

Query 1:

SELECT RID, ID, FirstName, LastName, Dept, ProductType AS Data, "PT" AS Cat FROM Table1
UNION SELECT RID, ID, FirstName, LastName, Dept, ProductName, "PN" FROM Table1
UNION SELECT RID, ID, FirstName, LastName, Dept, PurchaseDate, "PD" FROM Table1
UNION SELECT RID, ID, FirstName, LastName, Dept, PurchaseCost, "PC" FROM Table1
UNION SELECT RID, ID, FirstName, LastName, Dept, DeliveryDate, "DD" FROM Table1;

Query 2:

TRANSFORM First(Query1.Data) AS FirstOfData
SELECT Query1.ID, Query1.FirstName, Query1.LastName, Query1.Dept
FROM Query1
GROUP BY Query1.ID, Query1.FirstName, Query1.LastName, Query1.Dept
PIVOT DCount("*","Query1","ID=" & [ID] & " AND Cat='" & [Cat] & "' AND RID<" & [RID])+1 & [Cat];

However, there is a limit of 255 fields so there may be more data than can be handled.

Using WHERE NOT EXIST in a Transform/Pivot/CrossTab query

This line is an issue:

nwf.pwn_fullname = names_allpawns.pwn_fullname

you have nested this names_allpawns.pwn_fullname inside sub-query, which access does not recognize.

try to use joins instead, first catch full data, then put them inside TRANSFORM query.

Crosstab query with TRANSFORM and PIVOT is repeating rows

Your problem is that you want to TRANSFORM (i.e., report the value of) a.present but you have also included that field the GROUP BY clause. Try using TRANSFORM First(Abs(a.present)) and removing a.present from the GROUP BY clause, i.e.,

TRANSFORM First(Abs(a.present))
SELECT e.ID, e.firstName, e.lastName, e.position, e.rate 
FROM employees e LEFT JOIN attendance a ON e.ID = a.empID
GROUP BY e.ID, e.firstName, e.lastName, e.position, e.rate
PIVOT a.dateAttended

Converting a pivot table to a flat table in SQL

In order to get the result, you will need to UNPIVOT the data. When you unpivot you convert the multiple columns into multiple rows, in doing so the datatypes of the data must be the same.

I would use CROSS APPLY to unpivot the columns in pairs:

select t.employee_id,
  t.employee_name,
  c.data,
  c.old,
  c.new
from yourtable t
cross apply
(
  values 
  ('Address', Address_Old, Address_new),
  ('Income', cast(income_old as varchar(15)), cast(income_new as varchar(15)))
) c (data, old, new);

See SQL Fiddle with demo. As you can see this uses a cast on the income columns because I am guessing it is a different datatype from the address. Since the final result will have these values in the same column the data must be of the same type.

This can also be written using CROSS APPLY with UNION ALL:

select t.employee_id,
  t.employee_name,
  c.data,
  c.old,
  c.new
from yourtable t
cross apply
(
  select 'Address', Address_Old, Address_new union all
  select 'Income', cast(income_old as varchar(15)), cast(income_new as varchar(15))
) c (data, old, new)

See Demo

---

Related Topics