Unpivot in SSRS/Matrix

Question

I am trying to unpivot some data in SSRS but am struggling to get the format I want. My current table and data is shown below :-

CREATE TABLE [dbo].sampledata(
[DDMMMYY] [date] NULL,
[DayN] [nvarchar](4000) NULL,
[CArticle] [int] NULL,
[TU] [int] NULL,
[Pieces] [int] NULL,
[ActualSpace] [int] NULL,
[InternalCore] [int] NULL,
[QuarantinedStock] [int] NULL,
[AvailableSpace] [int] NULL
)


insert into sampledata (DDMMMYY, DayN, CArticle, TU, Pieces, ActualSpace, InternalCore, QuarantinedStock, AvailableSpace)
VALUES
('2019-09-13','Fri','848','20403','1249144','59790','17652','433','0'),
('2019-09-16','Mon','878','21328','1253811','63133','18908','429','0'),
('2019-09-17','Tue','892','21106','1253607','61770','18780','402','0'),
('2019-09-18','Wed','910','20948','1250381','61543','18485','955','0'),
('2019-09-19','Thu','863','20351','1243131','60235','18845','627','0'),
('2019-09-20','Fri','847','19923','1242594','59565','19460','1385','0'),
('2019-09-23','Mon','863','20862','1254736','62773','18362','1418','0'),
('2019-09-24','Tue','860','20592','1259028','62864','19972','1422','0'),
('2019-09-25','Wed','871','20646','1273306','63079','20498','1430','0'),
('2019-09-26','Thu','875','20424','1264449','61508','20040','1430','0'),
('2019-09-27','Fri','884','20581','1277418','62128','20287','1430','0'),
('2019-09-30','Mon','873','21684','1341305','66764','22666','1266','0');

I will never be returning more than 31 days worth of data.

What I want to do is unpivot the data but keep the group by based on date, my data should look like this when finished :-

I want the dates to run across the top of my data and the headings to run down the left side e.g. TU, Pieces etc, initially I used unpivot but as there are multiple days, the data did not stretch across to the right as I wanted by day.

I have tried using a SSRS matrix but still struggle to get the desired output.

Any help would be much appreciated.

I have now managed to achieve what I was looking to do using SSRS and a table Matrix, previously I was adding the fields that I wanted in to separate groups when I shouldn't of been, long time since I have done much in SSRS. Though I would be interested if the intended results can be achieved using tsql code though. — PJD, Oct 07 '19 at 13:21

score 0 · Accepted Answer · answered Oct 07 '19 at 18:59

Here's how I would UNPIVOT your data:

SELECT DDMMMYY, DayN, TypeCount, DataType
FROM #sampledata
UNPIVOT (TypeCount FOR DataType IN (CArticle
      ,TU
      ,Pieces
      ,ActualSpace
      ,InternalCore
      ,QuarantinedStock
      ,AvailableSpace )
      ) u

Personally, I would just use a DATE field and let SSRS figure out what day it is (Mon, Tue...) rather than have a DayN field.

Unpivot in SSRS/Matrix

1 Answers1