How to pivot without an aggregate?

Question

I have some sample data as listed below and am hoping someone can help me with it.

Date                    WellName   Reading
----------------------------------------
11/30/2018 12:00:00 AM    Well One   10
11/30/2018 12:00:00 AM    Well Two   20
11/30/2018 12:00:00 AM    Well Three 10
12/1/2018 12:00:00 AM     Well One   14
12/1/2018 12:00:00 AM     Well Two   25
12/1/2018 12:00:00 AM     Well Three 11
12/2/2018 12:00:00 AM     Well One   17
12/2/2018 12:00:00 AM     Well Two   43
12/2/2018 12:00:00 AM     Well Three 27
12/3/2018 12:00:00 AM     Well One   25
12/3/2018 12:00:00 AM     Well Two   56
12/3/2018 12:00:00 AM     Well Three 67
12/16/2018 12:00:00 AM    Well One   10
12/16/2018 12:00:00 AM    Well Three 10
12/16/2018 12:00:00 AM    Well Two   11

What I need to have is the dates listed across the top and thought a PIVOT might do the trick, but I can't seem to get it to work. I don't need to sum the values or anything, I just need the data flipped.

I also need to have a value when there is no date, so I assume I would need to create a date table & join that.

Any thoughts or suggestions?

Here is what I am trying to achieve (didn't add the columns for all the dates, but hopefully you get the idea)..11/30/2018,12/1/2018,12/2/2018,12/3/2018,12/4/2018...12/16/2018 Well One,10,14,17,25,0,...10 Well Two,20,25,43,56,0,...10 Well Three,10,11,27,67,0,...11 — Carl Brydon, Dec 17 '18 at 03:01
"I don't need to sum the values or anything, I just need the data flipped." You would still use an aggregate to PIVOT, since you will want to group by date and (presumably) nothing else. "I also need to have a value when there is no date, so I assume I would need to create a date table & join that." This is the sensible thing to do, yes. The other option is just doing it on the fly but I think this is overall more cumbersome. — ZLK, Dec 17 '18 at 03:03
typical thing in sql query when you want to use the aggregate function (but dont really need it) is to just use either min/max and it does the trick. Of course it is upto to make sure that your data is all correct otherwise the min/max may give inconsistent results. — peeyush singh, Dec 17 '18 at 03:08
Here's an example, since you are using dates as in the columns you will need to build a dynamic query: https://stackoverflow.com/questions/27422109/how-to-pivot-dynamically-with-date-as-column — peeyush singh, Dec 17 '18 at 03:15

score 0 · Answer 1 · edited Dec 17 '18 at 07:25

Date can be flipped but what values you are going to fill against the dates either wellname or reading.

Here I have used wellname. You can try the below code.

create table stk_test2(dt datetime,wellname varchar(15),reading int)
select * from stk_test2

declare @col nvarchar(max)
select @col = 
(select replace(replace(STUFF((SELECT distinct ',[' +
                       convert(varchar(20),dt,21) as dt
                      FROM stk_test2
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, ''),'.',''),',','],'))+']'

select @col

declare @sql nvarchar(max)
select
 @sql = 'select * from 
( 
select dt,wellname from stk_test2
)ds
pivot(
Min(wellname) 
for dt in ('+@col+')
)fropp'

EXEC Sp_executesql @sql

score 0 · Answer 2 · answered Jan 11 '19 at 18:52

Thanks for the suggestions everyone - all very useful. I ended up using the following query and throwing the result into a Cross-Tab component in the software I am using...

declare @DateTable table (date DATETIME,  PRIMARY KEY (date))

DECLARE
  @basedate DATETIME,
  @offset   INT
SELECT
  @basedate = '{varFrom}',
  @offset = 1
  INSERT INTO @DateTable SELECT @basedate

WHILE ( DATEADD(DAY, @offset, @basedate) <= '{varTo}')
BEGIN
  INSERT INTO @DateTable SELECT DATEADD(DAY, @offset, date) FROM @DateTable where DATEADD(DAY, @offset, date) <= '{varTo}'
  SELECT @offset = @offset + @offset
END

BEGIN
WITH cte AS
(
select wod.[Well One] as Reading,'Well One' as Well, Convert(date,wo.BeginDate) as BeginDate from [reports].[WorkOrders] wo
INNER JOIN reports.WorkOrderForm_WellReadingForm wod on wod.ID = wo.ID
UNION ALL
select wod.[Well Two] as Reading,'Well Two' as Well, Convert(date,wo.BeginDate) as BeginDate from [reports].[WorkOrders] wo
INNER JOIN reports.WorkOrderForm_WellReadingForm wod on wod.ID = wo.ID
UNION ALL
select wod.[Well Three] as Reading,'Well Three' as Well, Convert(date,wo.BeginDate) as BeginDate from [reports].[WorkOrders] wo
INNER JOIN reports.WorkOrderForm_WellReadingForm wod on wod.ID = wo.ID
),
CTE_Main (Well,BeginDate,ReadingChange) AS
(
SELECT rc.Well, rc.BeginDate, COALESCE(CAST(rc.Reading AS VARCHAR(20)) + ' (' + CASE WHEN rc.Reading < rp.Reading THEN '-' ELSE '+' END + CAST(rc.Reading - rp.Reading AS VARCHAR(20)) + ')',CAST(rc.Reading as VARCHAR(20))) as ReadingChange
FROM cte rc --reading current
LEFT JOIN cte rp --reading prior
    ON rc.Well = rp.Well
    AND rc.BeginDate = DATEADD(DAY,1,rp.BeginDate)
)
select well.Well,well.ReadingChange, CONVERT(date,cal.date) as date from @DateTable cal
LEFT JOIN CTE_Main well on well.BeginDate = cal.date
ORDER BY cal.date
END

How to pivot without an aggregate?

2 Answers2