October 10, 2017 at 3:52 pm
All,
Apologises if I'm asking too many questions at the moment. I am reading other posts to contribute if I can.
Would anyone mind advising on the following?:
If I run the following code:
set dateformat dmy select title, title as contacttype, logdate as logdate from
(select tabusrcontacttype.title, tabusrcontacttype.title as contacttype, datepart(week,logtime) as logdate from vrwCSLogs inner join tabusrcontacttype on contacttypeRef=tabusrcontacttype.uniqueref inner join tabusrcustomer on customerref=tabusrcustomer.uniqueref inner join standard..tabstaff staff on staffid=staff.uniqueref inner join tabusrproductgroup on productgroupref=tabusrproductgroup.uniqueref
where logtime>'01/01/2016 00:00:00' ) as src
It runs fine, I get a list as follows:
title contacttype logdate
Damaged in transit Damaged in transit 2
Damaged in transit Damaged in transit 4
Damaged in transit Damaged in transit 5
Damaged in transit Damaged in transit 6
Damaged in transit Damaged in transit 7
Damaged in transit Damaged in transit 9
If I add a pivot as follows:
set dateformat dmy select title, title as contacttype, logdate as logdate from
(select tabusrcontacttype.title, tabusrcontacttype.title as contacttype, datepart(week,logtime) as logdate from vrwCSLogs inner join tabusrcontacttype on contacttypeRef=tabusrcontacttype.uniqueref inner join tabusrcustomer on customerref=tabusrcustomer.uniqueref inner join standard..tabstaff staff on staffid=staff.uniqueref inner join tabusrproductgroup on productgroupref=tabusrproductgroup.uniqueref
where logtime>'01/01/2016 00:00:00' ) as src
PIVOT (count(contacttype) for logdate IN ([01],[02],[03],[04],[05])) prv
It complains it can't find logdate. It has to be the logdate in the pivot line that it can't find as that is all that changed.
If I change the first line to use 'select *' as follows:
set dateformat dmy select * from
(select tabusrcontacttype.title, tabusrcontacttype.title as contacttype, datepart(week,logtime) as logdate from vrwCSLogs inner join tabusrcontacttype on contacttypeRef=tabusrcontacttype.uniqueref inner join tabusrcustomer on customerref=tabusrcustomer.uniqueref inner join standard..tabstaff staff on staffid=staff.uniqueref inner join tabusrproductgroup on productgroupref=tabusrproductgroup.uniqueref
where logtime>'01/01/2016 00:00:00' ) as src
PIVOT (count(contacttype) for logdate IN ([01],[02],[03],[04],[05])) prv
It works OK.
Can anyone advise why it can't locate logdate in the second code block?
Thanks
October 10, 2017 at 4:57 pm
Because there is no column logdate. You are Pivoting on the column logdate (i.e. transforming it from a column to values in a single row). You can't pivot on a column and have it still exist in the output.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2017 at 4:37 am
Hello,
Thanks for your help. I hadn't thought of that.
However if I remove logdate from the select then it runs but there is no pivot. I just get two columns both containing the tabusrcontacttype.title data.
Maybe I need to add DDL and DML? There are quite a few tables so I thought that might just too much info if my query mistake was easy to find.
Thanks
Andrew
October 11, 2017 at 4:56 am
There should be a Pivot. For example, have a look at the following:USE Sandbox;
GO
CREATE TABLE #Sample (Title varchar(50), ContactType varchar(50), LogDate int);
GO
INSERT INTO #Sample
VALUES
('Damaged in transit','Damaged in transit',2),
('Damaged in transit','Damaged in transit',4),
('Damaged in transit','Damaged in transit',5),
('Damaged in transit','Damaged in transit',6),
('Damaged in transit','Damaged in transit',7),
('Damaged in transit','Damaged in transit',9);
GO
SELECT *
FROM #Sample;
GO
SELECT P.Title,
P.[01],P.[02],P.[03],P.[04],P.[05]
FROM
(SELECT *
FROM #Sample) S
PIVOT
(Count(ContactType) FOR LogDate IN ([01],[02],[03],[04],[05]))P
GO
DROP TABLE #Sample;
The First Select returns the normal 3NF data (yes, the allignment is off, because SSC and copy and Paste do not like each other, STILL -_-):Title ContactType LogDate
-------------------------------------------------- -------------------------------------------------- -----------
Damaged in transit Damaged in transit 2
Damaged in transit Damaged in transit 4
Damaged in transit Damaged in transit 5
Damaged in transit Damaged in transit 6
Damaged in transit Damaged in transit 7
Damaged in transit Damaged in transit 9
With the pivot you get:Title 01 02 03 04 05
-------------------------------------------------- ----------- ----------- ----------- ----------- -----------
Damaged in transit 0 1 0 1 1
As you can see, LogDate has "moved" (pivoted) from being a Column to 5 separate columns representing a different LogDate each (1, 2, 3, 4 and 5).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 11, 2017 at 6:24 am
Hello,
Thanks, your example made me understand the issue. Logdate can't be referenced in the select (because, as you said, it's being used for the pivot) but I can reference the headings created by the headings. (so '01','02') e.t.c.
Which is
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply