November 20, 2007 at 5:40 am
Hello,
I have for example the following information Ive generated. The table will be added to everytime I run the query, It adds another row for the date I ran the query.
Title total date
Q01 No of Child Records 19191815/06/2007
Q01 No of Child Records 19297026/07/2007
Q01 No of Child Records19788031/08/2007
Q01 No of Child Records20141615/10/2007
Q01 No of Child Records20174619/10/2007
Q01 No of Child Records20337407/11/2007
I want to be able to see the data like this
Title 15/06/200726/07/2007 31/08/2007
Q01 No of Child Records 191918 192970 197880
etc as a report Im doing for management. Then everytime i run the script again it will add extra columns for the dates.
Is there any way of doing this???? I thought about creating a temporary table of dates and then using this to split the data but other than this Im not sure.
Any help would be great
Thanks
Debbie
November 20, 2007 at 7:03 am
This is a slight modification of a Jeff Moden script:
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
--===== Populate the variables using info from the table
SET @SQLHead = 'SELECT'
SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)
+ 'SUM(CASE WHEN RunDate = '''
+ CAST(RunDate AS VARCHAR) + ''' THEN Records ELSE 0 END) AS '
+ '[' + CAST(RunDate AS VARCHAR) + ']'
FROM x
SELECT @SQLFoot = CHAR(13) + 'FROM x'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
Works well enough. You'd want to add where clauses, etc.
"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 20, 2007 at 7:06 am
thanks for that,
Ill have a try and see what I can do!
Debbie
November 20, 2007 at 7:13 am
Ive tried amending the script for my own table
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
--===== Populate the variables using info from the table
SET @SQLHead = 'SELECT'
SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)
+ 'SUM(CASE WHEN Quality_Date = '''
+ CAST(Quality_Date AS VARCHAR) + ''' THEN Records ELSE 0 END) AS '
+ '[' + CAST(Quality_Date AS VARCHAR) + ']'
FROM dbo.DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES x
SELECT @SQLFoot = CHAR(13) + 'FROM x'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
But its coming back with an error message:
SUM(CASE WHEN Quality_Date = 'Nov 7
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'Nov 7 2007 1
FROM x'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Nov 7 2007 1
FROM x'.
I dont really know what Im doing at this point so Im not sure how to resolve the error. Im sure once it runs through without errors i will be able to understand what it is though :hehe:
November 20, 2007 at 7:27 am
The 'FROM x' should be changed to 'FROM dbo.DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES' because X was just name of the test table that I cooked up to play with your data.
You can look at the Messages tab to see what the query looks like. In my case:
SELECT
SUM(CASE WHEN Quality_Date = 'Jun 15 2007 12:00AM' THEN Records ELSE 0 END) AS [Jun 15 2007 12:00AM],
SUM(CASE WHEN Quality_Date = 'Jul 26 2007 12:00AM' THEN Records ELSE 0 END) AS [Jul 26 2007 12:00AM],
SUM(CASE WHEN Quality_Date = 'Aug 31 2007 12:00AM' THEN Records ELSE 0 END) AS [Aug 31 2007 12:00AM],
SUM(CASE WHEN Quality_Date = 'Oct 15 2007 12:00AM' THEN Records ELSE 0 END) AS [Oct 15 2007 12:00AM]
FROM x
You're just generating an ad hoc SQL statement. Just put a comment in front of the execute statement and check the output in the Messages tab until it looks right. I don't see where, in your code, you'd be getting the unclosed quote.
"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 20, 2007 at 7:31 am
Ahh I see,
OK Ill try again!
November 20, 2007 at 7:32 am
I guess its because of truncation (as there must be enough rows to fill the limit)...
You need to modify the query...
1. Need to select distinct dates from the resultset
2. CAST(Quality_Date AS VARCHAR) needs to be changed to CONVERT(VARCHAR(10),Quality_Date,112) (112 is the ISO standard compliant)
SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)
+ 'SUM(CASE WHEN Quality_Date = '''
+ CONVERT(VARCHAR(10),Quality_Date,112) + ''' THEN Records ELSE 0 END) AS '
+ '[' + CAST(Quality_Date AS VARCHAR) + ']'
FROM ( SELECT DISTINCT Quality_Date FROM dbo.DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES ) x
--Ramesh
November 20, 2007 at 7:41 am
Ah, true. I thought we were dealing with only a few rows. If there are hundreds or thousands, this won't work well at all. As a matter of fact, if you're looking at more than 10 or so columns, it's going to make for one very ugly report and you may want to reconsider the approach, aggregate weekly or monthly or something. Then you wouldn't need to do this dynamic pivot of the data either.
"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 20, 2007 at 8:16 am
Thats right,
This is only one part of a large table which will grow and grow every time we refresh the data set. Its to basically trace the quality of data over a large time period.
Oh dear it sounds a lot more difficult that I thought:crazy:
November 20, 2007 at 8:20 am
No one is going to want to look at a report that has 43 columns, let alone 430 or 43000. You'd want to look at aggregating the data in some other fashion or just report by rows instead of columns. Although, again, no one looks at more than about two to three screens on Excel, assume about 100 rows. Past that, you'll want to aggregate.
See, the technical issues are the easy ones. That's why I like being a geek.
"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 20, 2007 at 8:25 am
Your right Ill definitely end up aggregating at some point, although Ill probably want to go for 6 months or something.
The data will probably end up being used to provide graphical information for managers as line charts to track the quality changes which is why I need the dates to become columns.
Im going to have to start working on what I WANT to see and then work backwards from there....
November 21, 2007 at 9:51 am
Ive spent all day trying to figure out how to do this one but Im still stumped :sick:
I know that this is what I want to do though.....
I need to turn this
TitleALL_RECSCIPCPEPBSS_PROVRUN DATE
Q01 No of Child Records 191918722209145815/06/2007
Q01 No of Child Records 192970736171132426/07/2007
Q01 No of Child Records197880760180132231/08/2007
Q01 No of Child Records201416781237130715/10/2007
Q01 No of Child Records201746780224134419/10/2007
Q01 No of Child Records203374800204135907/11/2007
Into this
TitleServices15/06/200726/07/200731/08/200715/10/200719/10/200707/11/2007
Q01 No of Child Records ALL_RECS191918192970197880201416201746203374
Q01 No of Child Records CIPC722736760781780800
Q01 No of Child RecordsPEP209171180237224204
Q01 No of Child RecordsBSS_PROV145813241322130713441359
Sorry the tables look a bit messy as they are unformated but basically the date fields will become columns and the services at the top will become fields under the column header services. Then the number of records under each column are placed horizonallty rather than vertically.
There are ALOT more questions and services within the main table but I have chosen this as an example.
Any help would be appreciated....
Thanks
Debbie
November 21, 2007 at 11:00 am
You're doing a full transpose - so it gets a bit messier than that... Assuming you DO have SQL 2005 (since that's the forum you're on), here's an UNPIVOT/PIVOT method for dynamically transposing.
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='testsvcs'
--===== Populate the variables using info from the table
SET @SQLHead = 'select pvt.*
from
(SELECT Title,convert(char,[run date],112) rundate,serviceType,Amount
from (select Title,[RUN DATE],all_recs,cipc,pep,bss_prov from '+@tablename+') p
UNPIVOT (AMOUNT for ServiceType in (all_recs,cipc,pep,bss_prov)) as unvpt) p2
PIVOT (max(amount) for rundate in ('
SELECT @SQLBody = ISNULL(@SQLBody+'],[','[') +rtrim(convert(varchar,[run date],112))
FROM testsvcs --also replace here with your table name
SELECT @SQLFoot = '])) pvt'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
----------------------------------------------------------------------------------
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 23, 2007 at 2:02 am
I do have 2005 so Ill give it a go but this is something far advanced to what Im used too 🙁
Ill let you know how I get on. I was really hoping that there would be something simple that I was missing but Im beginning to doubt it now. Scary!
Debbie
November 23, 2007 at 2:28 am
Not got very far with this one.
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
declare @tablename varchar(100)
--my test table is call DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES
set @tablename='DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES'
--===== Populate the variables using info from the table
SET @SQLHead = 'select pvt.*
from
(SELECT Title,convert(char,[Quality_Date],112) rundate,serviceType,Amount
from (select Title,QUALITY_DATE,all_recs,cipc,pep,bss_prov from '+@tablename+') p
UNPIVOT (AMOUNT for ServiceType in (all_recs,cipc,pep,bss_prov)) as unvpt) p2
PIVOT (max(amount) for rundate 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 = '])) pvt'
--===== Print the command we formed and execute it
PRINT @SQLHead+@SQLBody+@SQLFoot
EXEC (@SQLHead+@SQLBody+@SQLFoot)
ran this but Im getting an error of
select pvt.*
from
(SELECT Title,convert(char,[Quality_Date],112) rundate,serviceType,Amount
from (select Title,QUALITY_DATE,all_recs,cipc,pep,bss_prov from DATA_QUALITY_OVERVIEW_TOTALS_AND_PERCENTAGES) p
UNPIVOT (AMOUNT for ServiceType in (all_recs,cipc,pep,bss_prov)) as unvpt) p2
PIVOT (max(amount) for rundate 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])) pvt
Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'UNPIVOT'.
I think this is going to be slightly out of my league. Is this the only way of rejigging?
Debbie
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply