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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Columns to Rows and a Sum
 Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

emailuser
Yak Posting Veteran

74 Posts

Posted - 11/19/2013 :  13:19:00  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/20/2013 :  01:55:55  Show Profile  Reply with Quote

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)


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

emailuser
Yak Posting Veteran

74 Posts

Posted - 11/20/2013 :  04:25:18  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/20/2013 :  07:50:09  Show Profile  Reply with Quote
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 - 11/20/2013 :  08:16:21  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/20/2013 :  09:01:53  Show Profile  Reply with Quote
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 - 11/20/2013 :  09:24:23  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/20/2013 :  12:57:58  Show Profile  Reply with Quote
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 - 11/21/2013 :  04:22:10  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/21/2013 :  06:55:13  Show Profile  Reply with Quote
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 - 11/21/2013 :  08:45:20  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/21/2013 :  08:52:27  Show Profile  Reply with Quote
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 - 11/21/2013 :  09:14:23  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/21/2013 :  09:35:28  Show Profile  Reply with Quote
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 - 11/21/2013 :  10:06:47  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/22/2013 :  05:48:36  Show Profile  Reply with Quote
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 - 11/22/2013 :  07:02:14  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/22/2013 :  07:08:09  Show Profile  Reply with Quote
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 - 11/22/2013 :  07:19:03  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/22/2013 :  07:24:05  Show Profile  Reply with Quote

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)


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

emailuser
Yak Posting Veteran

74 Posts

Posted - 11/22/2013 :  10:40:39  Show Profile  Reply with Quote
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
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000