SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
52325 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
52325 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
52325 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
52325 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
52325 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
52325 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
52325 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
52325 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
52325 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
 New Topic  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.34 seconds. Powered By: Snitz Forums 2000