November 23, 2007 at 8:21 am
Well,
I tried doing this in Excel and managed to jig it to look right using the pivot table and charts, but there seems to be some kind of bug in in where it splits up data in the title bar.
So you get
Q1. All records
Q1. All records
If you click on the first one you get 3 columns of dates and if you click on the sedond one you get the last 2 columns. Very bizarre. Im beginning to really not enjoy this task :unsure:
However, if its possible in Excel it just has to be possible in SQL doesnt it???
November 26, 2007 at 8:04 am
Hi again,
I tried using the PIVOT and UNPIVOT script simplifying it to see if I could get rid of the error
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
declare @tablename varchar(100)
--my test table is call testsvcs - put yours in
set @tablename='DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES'
SELECT * FROM DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES
--===== Populate the variables using info from the table
SET @SQLHead = 'select dqr.*
from
(SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amount
from (select Title,Quality_date,TOT_ALL_RECS from '+@tablename+') p
UNPIVOT (AMOUNT for ServiceType in (TOT_ALL_RECS)) as unvpt) p2
PIVOT (max(amount) for Quality_date in ('
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,Quality_date,112))
FROM DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES --also replace here with your table name
SELECT @SQLFoot = '])) dqr'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
And Im still getting the error
(163 row(s) affected)
select dqr.*
from
(SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amount
from (select Title,Quality_date,TOT_ALL_RECS from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) p
UNPIVOT (AMOUNT for ServiceType in (TOT_ALL_RECS)) as unvpt) p2
PIVOT (max(amount) for Quality_date in ([20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070726],[20070831],[20071015],[20071019],[20071107],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20071015],[20071019],[20071107],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107],[20070615],[20070726],[20070831],[20071015],[20071019],[20071107])) dqr
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'UNPIVOT'.
Again I really cant see the error near UNPIVOT although Im becoming abit more familiar in what the script is doing. Is their anything obvious within the code that Im missing???.......
Debbie
November 26, 2007 at 8:18 am
Is it "as unvpt"?
I'm not very acquainted at all with the PIVOT & UNPIVOT operators, so that's a guess.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2007 at 8:23 am
Thanks for that, Ill have a look........
This PIVOT and UNPIVOT stuff is not much fun :doze:
November 26, 2007 at 10:20 am
Hmmm,
big question but when you say Is it "as unvpt"?
where in the script would that go?
Debbie
November 26, 2007 at 10:28 am
Just as an extra I put the pivot query as is into 2005 and created a table etc so I amended nothing from the original query. I then ran it again and it still comes up with the same error so at least I know its nothing I have done when I amended to look at my own table...
Still cant figure it out though :crying:
November 26, 2007 at 11:05 am
never mind the dynamic right now - what do you get when you run these two statements?
--statement #1
select p.*
from
(SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amount
from (select Title,Quality_date,TOT_ALL_RECS from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) p
--statement #2
select p2.*
from
(SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amount
from (select Title,Quality_date,TOT_ALL_RECS from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) p
UNPIVOT (AMOUNT for ServiceType in (TOT_ALL_RECS)) as unvpt) p2
There's an issue with the pivot statement, since you're generating mutiple columns of the same name (a no-no in the PIVOT syntax). We'll have to work that out separately. The "real" pivot statement can ONLY contain one instance of 20070615, for example.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2007 at 11:07 am
if you replace this line in the ORIGINAL code I posted
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,[run date],112))
FROM testsvcs --also replace here with your table name
with....
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,[run date],112))
FROM (select distinct [run date] from testsvcs) t --also replace here with your table name
your PIVOT should work. Sorry - missed that little ditty...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2007 at 11:14 am
It's already in the script, but it doesn't look right to my (very untrained regarding PIVOT/UNPIVOT) eye.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2007 at 11:42 am
pvt, unpvt, p and p2 are (admittedly) ugly, but short, aliases for the various sub-queries that syntax creates. It does make it a bit harder to read, but I was trying to save some real estate, since the # of columns was so large.
Of course - no matter how you slice it - the PIVOT won't work with those column names, since you have dupes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 27, 2007 at 2:00 am
never mind the dynamic right now - what do you get when you run these two statements?
--statement #1select p.* from (SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amountfrom (select Title,Quality_date,TOT_ALL_RECS from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) p--statement #2select p2.*from (SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amountfrom (select Title,Quality_date,TOT_ALL_RECS from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) pUNPIVOT (AMOUNT for ServiceType in (TOT_ALL_RECS)) as unvpt) p2
There's an issue with the pivot statement, since you're generating mutiple columns of the same name (a no-no in the PIVOT syntax). We'll have to work that out separately. The "real" pivot statement can ONLY contain one instance of 20070615, for example.
-------------------------------------------------------------------
both queries come back with errors.
Query 1 comes back with
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'p'.
Query 2 comes back with
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'UNPIVOT'.
Im running them in 2005 so thats not the problem. Every time I try to do any of these queries its always the UNPIVOT that seems to cause the error
Debbie
November 27, 2007 at 2:16 am
Matt Miller (11/26/2007)
if you replace this line in the ORIGINAL code I posted
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,[run date],112))
FROM testsvcs --also replace here with your table name
with....
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,[run date],112))
FROM (select distinct [run date] from testsvcs) t --also replace here with your table name
your PIVOT should work. Sorry - missed that little ditty...
I couldnt really tell the difference but I changed the script to
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
declare @tablename varchar(100)
--my test table is call testsvcs - put yours in
set @tablename='DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES'
SELECT * FROM DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES
--===== Populate the variables using info from the table
SET @SQLHead = 'select dqr.*
from
(SELECT Title,convert(char,Quality_date,112) Quality_date,serviceType,Amount
from (select Title,Quality_date,TOT_ALL_RECS from '+@tablename+') p
UNPIVOT (AMOUNT for ServiceType in (TOT_ALL_RECS)) as unvpt) p2
PIVOT (max(amount) for Quality_date in ('
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,Quality_date,112))FROM (select distinct Quality_date
from dbo.DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) t
SELECT @SQLFoot = '])) dqr'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
and its still generating the same error message near the unpivot bit..... Not having any luck with this at all am I ??? 🙂
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply