SQL Server Pivot Data without aggregation

Question

I know this has been asked many, many times before, but I struggle to find the solution to what I need.

Data:

Id   FKId   TypeId   Score   ScoreDate
1    317    1        90      2019-01-01
2    317    1        80      2019-01-02
3    317    2        65      2019-01-03
4    317    2        80      2019-01-04
5    318    1        82      2019-01-01
6    318    1        78      2019-01-02
7    318    2        55      2019-01-03
8    318    2        63      2019-01-04

Expected Result:

FkId   A1   A2   B1  B2
317    90   80   65  80
318    82   78   55  63

I tried to use a pivot to get me data, by the bit that I stumble over is that you need to use an aggregate function and that result in the wrong result. Example of one of the attempts:

select *
from 
(
  select [FkId], [TypeId], [Score]
  from #yt
) src
pivot
(
  max([Score])
  for [TypeId] in ([1], [2], [3])
) piv;

query result:
FkId  1     2       3
101   83    82  NULL
102   96    80  NULL

I understand that I will need to have more than one query to get to the expected result, but that is not my concern, for now I just want to transpose one type of score with each score into it's own column.

Any help will do.

score 2 · Accepted Answer · answered Feb 09 '19 at 19:43

Try this:

CREATE TABLE #yt
(
    [Id] INT
   ,[FKId] INT
   ,[TypeId] TINYINT
   ,[Score] INT
   ,[ScoreDate] DATE
)

INSERT INTO #yt
VALUES (1, 317, 1, 90, '2019-01-01')
      ,(2, 317, 1, 80, '2019-01-02')
      ,(3, 317, 2, 65, '2019-01-03')
      ,(4, 317, 2, 80, '2019-01-04')
      ,(5, 318, 1, 82, '2019-01-01')
      ,(6, 318, 1, 78, '2019-01-02')
      ,(7, 318, 2, 55, '2019-01-03')
      ,(8, 318, 2, 63, '2019-01-04');

select *
from 
(
    SELECT [FKId]
        ,CHAR(64 + DENSE_RANK() OVER (PARTITION BY FKID ORDER BY TypeID)) + CAST(DENSE_RANK() OVER (PARTITION BY FKID, TypeID ORDER BY [ScoreDate]) AS VARCHAR(12))
        ,[Score]
    FROM #yt
) src ([FKId], [TypeID], [Score])
pivot
(
  max([Score])
  for [TypeId] in ([A1], [A2], [B1], [B2])
) piv;


DROP TABLE #yt;

or if you want to make it dynamic:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@DynamicColumns NVARCHAR(MAX)

SET @DynamicColumns = STUFF
                        (
                            (
                                SELECT DISTINCT ',[' + CHAR(64 + DENSE_RANK() OVER (PARTITION BY FKID ORDER BY TypeID)) + CAST(DENSE_RANK() OVER (PARTITION BY FKID, TypeID ORDER BY [ScoreDate]) AS VARCHAR(12)) + ']'
                                FROM #yt                               
                                FOR XML PATH('') ,TYPE
                            ).value('.', 'NVARCHAR(MAX)')
                            ,1
                            ,1
                            ,''
                        );

SET @DynamicTSQLStatement = N'
select *
from 
(
    SELECT [FKId]
        ,CHAR(64 + DENSE_RANK() OVER (PARTITION BY FKID ORDER BY TypeID)) + CAST(DENSE_RANK() OVER (PARTITION BY FKID, TypeID ORDER BY [ScoreDate]) AS VARCHAR(12))
        ,[Score]
    FROM #yt
) src ([FKId], [TypeID], [Score])
pivot
(
  max([Score])
  for [TypeId] in ('+@DynamicColumns+')
) piv;'


EXEC sp_executesql @DynamicTSQLStatement;

score 2 · Answer 2 · answered Feb 09 '19 at 20:01

Check this: I used two queries then union them and took max of its values

CREATE TABLE t (
  "Id" INTEGER,
  "FKId" INTEGER,
  "TypeId" INTEGER,
  "Score" INTEGER,
  "ScoreDate" date
);


GO

✓

INSERT INTO t
  ("Id", "FKId", "TypeId", "Score", "ScoreDate")
VALUES
  ('1', '317', '1', '90', '2019-01-01'),
  ('2', '317', '1', '80', '2019-01-02'),
  ('3', '317', '2', '65', '2019-01-03'),
  ('4', '317', '2', '80', '2019-01-04'),
  ('5', '318', '1', '82', '2019-01-01'),
  ('6', '318', '1', '78', '2019-01-02'),
  ('7', '318', '2', '55', '2019-01-03'),
  ('8', '318', '2', '63', '2019-01-04');
GO

8 rows affected

select [FkId], max([1]) as c1, max([2]) as c2, max([3]) as c3, max([4]) as c4 from
(
select FkId, [1], [2], [3], [4] from
(
  select *
  from
  (
    select [FkId], [TypeId], [Score]
    from t
  ) src
  pivot
  (
    max([Score])
    for [TypeId] in ([1], [2] , [3], [4])
  ) as Piv1
) P
union all
select FkId, null, null, [1], [2] from
(
  select *
  from
  (
    select [FkId], [TypeId], [Score]
    from t
  ) src
  pivot
  (
    min([Score])
    for [TypeId] in ( [1], [2])
  ) as Piv2
) P2
) PP
group by FkId
GO

FkId | c1 | c2 | c3 | c4
---: | -: | -: | -: | -:
 317 | 90 | 80 | 80 | 65
 318 | 82 | 63 | 78 | 55

db<>fiddle here

SQL Server Pivot Data without aggregation

2 Answers2