Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Columns to Rows and a Sum

Author  Topic 

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-19 : 13:19:00
Hi everyone , i would greatly appreciate anyones help on the following that i am trying to solve ...

I am using SQL 2008
i have a database called ISCmetrics and a table called Meeting, the table meeting has 5 columns id ( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)



We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .


The output for a Select * from ISCMetricslooks like this

id TEAMNAME DATECOMPLETE STATUSNAME STATUSLEVEL
1 TeamA 20131022 STATCONTACT 1
2 TeamA 20131022 STATACTION 1
3 TeamA 20131022 STATABC 1

4 TeamB 20131022 STATCONTACT 1
5 TeamB 20131022 STATCTION 0
6 TeamB 20131022 STATABC 1

7 TeamA 20131021 STATCONTACT 0
8 TeamA 20131021 STATACTION 1
9 TeamA 20131021 STATABC 0

10 TeamB 20131021 STATCONTACT 1
11 TeamB 20131021 STATACTION 1
12 TeamB 20131021 STATABC 1






What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....


TEAMNAME 20131022 20131021
TeamA 3 1
TeamB 2 3


its mind boggling for me so any help Massively appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 01:55:55
[code]
DECLARE @DateList varchar(5000)

SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM ISCMetricslooks ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM ISCMetricslooks
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-20 : 04:25:18
Hi Visakh , thanks for your reply ,
I received error that @SQL not defined , so I guess I need to DECLARE @SQL varchar(5000) ? is that correct ..

you have a FROM ISCMetricslooks ... should this be from ISCMetrics.Meeting ( database and table name ) ?

I did change both the above on the code to
DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(5000)

SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM ISCMetrics.E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM ISCMetrics.E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


and received the errors

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.


Appreciate any further help you can give me
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 07:50:09
i dont think error is from posted part as i cant see where you're using SELECT INTO.
Please check the rest of batch if you're using this in a batch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-20 : 08:16:21
Hi Visakh16 , aah you are right ... sorry for that , just a syntax error now
DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(5000)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '.'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 09:01:53
replace EXEC(@SQL) with PRINT(@SQL) and post back the result.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-20 : 09:24:23
Hi ... here it is :)

SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 12:57:58
ok. i got the issue. It overshot the max length of @SQL so increase it


DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(5000max)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-21 : 04:22:10
Hi Visakh , seems so close just a syntax error still

DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)



Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 06:55:13
show result of PRINT @SQL please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-21 : 08:45:20
Hi , here you go ,,, if it helps I only need data from current year
SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 08:52:27
its not showing full statement.. can you increase number of characters to be displayed for resuiltset in SSMS and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-21 : 09:14:23
Hi Increased to 1000 , think the results are the same

SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 09:35:28
increase to maximum possible (8000)
otherwise dump results to file and then copy and paste

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-21 : 10:06:47
hi increased to 8000 and sent to file ... it looks the same :(

SELECT TEAMNAME,[20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20110915],[20110916],[20110919],[20110920],[20111227],[20111228],[20120229],[20120301],[20120302],[20120305],[20120306],[20120307],[20120308],[20120309],[20120312],[20120313],[20120314],[20120315],[20120316],[20120319],[20120320],[20120321],[20120322],[20120323],[20120326],[20120327],[20120328],[20120329],[20120330],[20120402],[20120403],[20120404],[20120405],[20120409],[20120410],[20120411],[20120412],[20120413],[20120416],[20120417],[20120418],[20120419],[20120420],[20120423],[20120424],[20120425],[20120426],[20120427],[20120430],[20120501],[20120502],[20120503],[20120504],[20120507],[20120508],[20120509],[20120510],[20120511],[20120514],[20120515],[20120516],[20120517],[20120518],[20120521],[20120522],[20120523],[20120524],[20120525],[20120528],[20120529],[20120530],[20120531],[20120601],[20120605],[20120606],[20120607],[20120608],[20120611],[20120612],[20120613],[20120614],[20120615],[20120618],[20120619],[20120620],[20120621],[20120622],[20120625],[20120626],[20120627],[20120628],[20120629],[20120702],[20120703],[20120704],[20120705],[20120706],[20120709],[20120710],[20120711],[20120712],[20120713],[20120716],[20120717],[20120718],[20120719],[20120720],[20120723],[20120724],[20120725],[20120726],[20120727],[20120730],[20120731],[20120801],[20120802],[20120803],[20120806],[20120807],[20120808],[20120809],[20120810],[20120813],[20120814],[20120815],[20120816],[20120817],[20120820],[20120821],[20120822],[20120823],[20120824],[20120827],[20120828],[20120829],[20120830],[20120831],[20120903],[20120904],[20120905],[20120906],[20120907],[20120910],[20120911],[20120912],[20120913],[20120914],[20120917],[20120918],[20120919],[20120920],[20120921],[20120924],[20120925],[20120926],[20120927],[20120928],[20121001],[20121002],[20121003],[20121004],[20121005],[20121008],[20121009],[20121010],[20121011],[20121012],[20121015],[20121016],[20121017],[20121018],[20121019],[20121022],[20121023],[20121024],[20121025],[20121026],[20121029],[20121030],[20121031],[20121101],[20121102],[20121105],[20121106],[20121107],[20121108],[20121109],[20121112],[20121113],[20121114],[20121115],[20121116],[20121119],[20121120],[20121121],[20121122],[20121123],[20121125],[20121126],[20121127],[20121128],[20121129],[20121130],[20121202],[20121203],[20121204],[20121205],[20121206],[20121207],[20121209],[20121210],[20121211],[20121212],[20121213],[20121214],[20121216],[20121217],[20121218],[20121219],[20121220],[20121221],[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 05:48:36
do one more than restruict it to year and see

DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-22 : 07:02:14
Hi Visakh16 , really appreciate your patience :) and help ...

this time I get
Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for sum operator.

A print gives the following

SELECT TEAMNAME,[20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122]
FROM
(
SELECT TEAMNAME, DATECOMPLETE,STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101'
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN ([20130101],[20130102],[20130103],[20130104],[20130106],[20130107],[20130108],[20130109],[20130110],[20130111],[20130113],[20130114],[20130115],[20130116],[20130117],[20130118],[20130120],[20130121],[20130122],[20130123],[20130124],[20130125],[20130127],[20130128],[20130129],[20130130],[20130131],[20130201],[20130203],[20130204],[20130205],[20130206],[20130207],[20130208],[20130210],[20130211],[20130212],[20130213],[20130214],[20130215],[20130217],[20130218],[20130219],[20130220],[20130221],[20130222],[20130224],[20130225],[20130226],[20130227],[20130228],[20130301],[20130303],[20130304],[20130305],[20130306],[20130307],[20130308],[20130310],[20130311],[20130312],[20130313],[20130314],[20130315],[20130317],[20130318],[20130319],[20130320],[20130321],[20130322],[20130324],[20130325],[20130326],[20130327],[20130328],[20130401],[20130402],[20130403],[20130404],[20130405],[20130407],[20130408],[20130409],[20130410],[20130411],[20130412],[20130414],[20130415],[20130416],[20130417],[20130418],[20130419],[20130421],[20130422],[20130423],[20130424],[20130425],[20130426],[20130428],[20130429],[20130430],[20130501],[20130502],[20130503],[20130506],[20130507],[20130508],[20130509],[20130510],[20130512],[20130513],[20130514],[20130515],[20130516],[20130517],[20130519],[20130520],[20130521],[20130522],[20130523],[20130524],[20130527],[20130528],[20130529],[20130530],[20130531],[20130602],[20130603],[20130604],[20130605],[20130606],[20130607],[20130609],[20130610],[20130611],[20130612],[20130613],[20130614],[20130616],[20130617],[20130618],[20130619],[20130620],[20130621],[20130623],[20130624],[20130625],[20130626],[20130627],[20130628],[20130630],[20130701],[20130702],[20130703],[20130704],[20130705],[20130707],[20130708],[20130709],[20130710],[20130711],[20130712],[20130714],[20130715],[20130716],[20130717],[20130718],[20130719],[20130721],[20130722],[20130723],[20130724],[20130725],[20130726],[20130728],[20130729],[20130730],[20130731],[20130801],[20130802],[20130804],[20130805],[20130806],[20130807],[20130808],[20130809],[20130812],[20130813],[20130814],[20130815],[20130816],[20130818],[20130819],[20130820],[20130821],[20130822],[20130823],[20130826],[20130827],[20130828],[20130829],[20130830],[20130901],[20130902],[20130903],[20130904],[20130905],[20130906],[20130908],[20130909],[20130910],[20130911],[20130912],[20130913],[20130915],[20130916],[20130917],[20130918],[20130919],[20130920],[20130922],[20130923],[20130924],[20130925],[20130926],[20130927],[20130929],[20130930],[20131001],[20131002],[20131003],[20131004],[20131006],[20131007],[20131008],[20131009],[20131010],[20131011],[20131013],[20131014],[20131015],[20131016],[20131017],[20131018],[20131020],[20131021],[20131022],[20131023],[20131024],[20131025],[20131027],[20131028],[20131029],[20131030],[20131031],[20131101],[20131103],[20131104],[20131105],[20131106],[20131107],[20131108],[20131110],[20131111],[20131112],[20131113],[20131114],[20131115],[20131117],[20131118],[20131119],[20131120],[20131121],[20131122]))p
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 07:08:09
whats the datatype of statuslevel? try this too

DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS int) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-22 : 07:19:03
Ooooh getting close :) , I did get some results in the form

TeamName Date1,Date2,Date3 etc ... but nothing populated

Error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '117.55' to data type int.

table meeting has 5 columns id ( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 07:24:05
[code]
DECLARE @DateList varchar(5000)
DECLARE @SQL varchar(MAX)
SET @DateList = STUFF((SELECT DISTINCT ',[' + DATECOMPLETE + ']' FROM E301836.[Log-TierMeeting] WHERE DATECOMPLETE >='20130101' AND DATECOMPLETE < '20140101' ORDER BY ',[' + DATECOMPLETE + ']' FOR XML PATH('')),1,1,'')
SET @SQL='SELECT TEAMNAME,' + @DateList + '
FROM
(
SELECT TEAMNAME, DATECOMPLETE,CAST(STATUSLEVEL AS decimal(10,2)) AS STATUSLEVEL
FROM E301836.[Log-TierMeeting]
WHERE DATECOMPLETE >=''20130101'' AND DATECOMPLETE < ''20140101''
)t
PIVOT (SUM(STATUSLEVEL) FOR DATECOMPLETE IN (' + @DateList + '))p'
EXEC (@SQL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

emailuser
Yak Posting Veteran

74 Posts

Posted - 2013-11-22 : 10:40:39
Visakh16 5 STARS !!!! ... thank you !! ... this gives us a full date span ... one last thing if possible is it possible to have a rolling 30 day span , i.e where we show the latest DATECOMPLETE ( largest I guess as its just a number not a date )and then the preceeding 30 days ( DATECOMPLETE) , so in effect the WHERE is always WHERE DATECOMPLETE is the largest value AND Largest DATECOMPLETE -30 ???? hope that makes sense ( I guess you could order the results by DATECOMPLETE DESC and use TOP30 could you ??)
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -