| Author |
Topic |
|
mossbs
Starting Member
11 Posts |
 Posted - 2013-10-30 : 06:56:06
|
| Hi guys, Is it possible to have a dynamically created select statement that selects columns based on a value in a table?Basically I have a report script that does a count of how many times a certain job appears on quotes...this was fine when first launched but now new jobs can be added and old ones deactivated. Each job has a entry in a JobDefinition table with a 'Status' column showing 'ATV' (active) or 'INA' (inactive).So... Is it possible to include a count for each of the jobs only where they are active?Cheers guys - any help much appreciated! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-10-30 : 06:59:03
|
| Start with thisselect job,count(*) from table where Status='ATV' group by jobMadhivananFailing to plan is Planning to fail |
 |
|
|
mossbs
Starting Member
11 Posts |
Posted - 2013-10-30 : 07:05:06
|
| Cheers for the reply dude - I possibly should've mentioned that the output format I need is that each of these jobs has its own column...as they are grouped by DealerName so each dealer has a seperate row showing how many of job X Y Z etc it has done...something along the lines ofDealerName - job1 - job2 - job3 - job4... etc etc TestDealer 1 3 0 0 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-30 : 08:03:22
|
use pivot for thatSELECT *FROM(SELECT JobIDFROM JobDefinitionWHERE Status = 'ATV')tPIVOT (SUM(1) FOR JobID IN ([Job1],[Job2],..))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
mossbs
Starting Member
11 Posts |
Posted - 2013-10-30 : 10:23:43
|
| Cheers for reply... Not used PIVOT before, but just given it a go...might have a problem..Seen as though the JobIds could be different each time - how do I make the PIVOT part of the query dynamically create the IN list... as it seems I need to hard code values in for that part? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
mossbs
Starting Member
11 Posts |
Posted - 2013-10-31 : 05:58:45
|
| Awesome - nice one thank you mate!Looks like this is what I'm after - clever stuff eh?!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-31 : 06:03:08
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
mossbs
Starting Member
11 Posts |
Posted - 2013-11-06 : 08:47:18
|
| Cheers for the help on this... it has worked perfectly for producing the results in SQL... My next question though (sorry.. always something else eh?!)...Is there a way to export this to a csv file?Normally I would use a SSIS package - to export to csv, then to email... - however - as the result set is dynamic and can be a different set of columns each time - I cant provide SSIS with the columns or metadata...again - any help greatly appreciated! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-06 : 08:59:13
|
quote:
Originally posted by mossbs
Cheers for the help on this... it has worked perfectly for producing the results in SQL... My next question though (sorry.. always something else eh?!)...Is there a way to export this to a csv file?Normally I would use a SSIS package - to export to csv, then to email... - however - as the result set is dynamic and can be a different set of columns each time - I cant provide SSIS with the columns or metadata...again - any help greatly appreciated!
You can use bcpseehttp://visakhm.blogspot.in/2013/10/different-ways-to-export-sql-server.htmlJust use file extension as .csv instead of .txt in above example------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
mossbs
Starting Member
11 Posts |
Posted - 2013-11-07 : 10:57:47
|
| Cheers dude - used bcp- but then had problem of it not exporting column headers... So - (just for info) - what I did was - bcp out the results from my query- bcp out the column headers into a separate file (using a function that comma separates the column names from the INFORMATION_SCHEMA.COLUMNS view) - Use a bat file to combine these two csv files into one.Bit of a long winded way to set up - but it works a charm!Many thanks for your help dude! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-07 : 11:43:08
|
quote:
Originally posted by mossbs
Cheers dude - used bcp- but then had problem of it not exporting column headers... So - (just for info) - what I did was - bcp out the results from my query- bcp out the column headers into a separate file (using a function that comma separates the column names from the INFORMATION_SCHEMA.COLUMNS view) - Use a bat file to combine these two csv files into one.Bit of a long winded way to set up - but it works a charm!Many thanks for your help dude!
No problemyou're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-11-11 : 02:57:16
|
quote:
Originally posted by mossbs
Cheers dude - used bcp- but then had problem of it not exporting column headers... So - (just for info) - what I did was - bcp out the results from my query- bcp out the column headers into a separate file (using a function that comma separates the column names from the INFORMATION_SCHEMA.COLUMNS view) - Use a bat file to combine these two csv files into one.Bit of a long winded way to set up - but it works a charm!Many thanks for your help dude!
Yes that's the way to do it when you use Dynamic SQLMadhivananFailing to plan is Planning to fail |
|
|
|
|
Dynamically select columns based on value in table