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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Dynamically select columns based on value in table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mossbs
Starting Member

8 Posts

Posted - 10/30/2013 :  06:56:06  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 10/30/2013 :  06:59:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Start with this

select job,count(*) from table where Status='ATV' group by job

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mossbs
Starting Member

8 Posts

Posted - 10/30/2013 :  07:05:06  Show Profile  Reply with Quote
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 of

DealerName - job1 - job2 - job3 - job4... etc etc
TestDealer 1 3 0 0



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  08:03:22  Show Profile  Reply with Quote
use pivot for that

SELECT *
FROM
(
SELECT JobID
FROM JobDefinition
WHERE Status = 'ATV'
)t
PIVOT (SUM(1) FOR JobID IN ([Job1],[Job2],..))p


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

mossbs
Starting Member

8 Posts

Posted - 10/30/2013 :  10:23:43  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/30/2013 :  12:09:10  Show Profile  Reply with Quote
ok...here you go with dynamic PIVOT

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

mossbs
Starting Member

8 Posts

Posted - 10/31/2013 :  05:58:45  Show Profile  Reply with Quote
Awesome - nice one thank you mate!

Looks like this is what I'm after - clever stuff eh?!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/31/2013 :  06:03:08  Show Profile  Reply with Quote
welcome

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

mossbs
Starting Member

8 Posts

Posted - 11/06/2013 :  08:47:18  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2013 :  08:59:13  Show Profile  Reply with Quote
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 bcp
see

http://visakhm.blogspot.in/2013/10/different-ways-to-export-sql-server.html

Just use file extension as .csv instead of .txt in above example

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

mossbs
Starting Member

8 Posts

Posted - 11/07/2013 :  10:57:47  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/07/2013 :  11:43:08  Show Profile  Reply with Quote
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 problem
you're welcome

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 11/11/2013 :  02:57:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 SQL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.19 seconds. Powered By: Snitz Forums 2000