Using database fields as query columns

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Using database fields as query columns

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-13 : 15:58:25

I've done a little bit of searching and found two possible ways of doing this but want to get all of your input first.

I have a query that gives the total for each class.

SELECT     Class.Name, COUNT(CASE WHEN Member.status = '1' AND classID IS NOT NULL 
                      THEN Member.ID ELSE NULL END) AS [Active Members]
FROM         Member INNER JOIN
                      Contracts ON Member.ContractID = Contracts.ContractID INNER JOIN
                      Facility ON Member.FacilityID = Facility.FacilityID INNER JOIN
                      Class ON Contracts.ClassId = Class.ClassID LEFT OUTER JOIN
                      [#PrimaryClasses] ON classID = Contracts.ClassID
WHERE     (Facility.Name <> 'Corporate') AND (Member.Category <> 'C') and classid is not null
GROUP BY Class.Name

Right now it results similar to...

ClassName   Active Members
class 1        4
class 2        2
etc

I'd like the Facility.Name to be the columns. ex.

ClassName   Facility1   Facility2   Facility3
class 1        2           2           0
class 2        0           1           1
etc

MichaelP
Jedi Yak

2489 Posts

Posted - 2009-05-13 : 17:03:49

You need to look into dynamic crosstab I think. Do some seraching on this site and on the internet for dynamic crosstab.

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of Microsoft Corp. All information is provided "AS IS" with no warranties and confers no rights.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-14 : 10:23:11

Thanks. Found some good articles but definitely beyond my skill set for now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:28:33

what all are values avaiable inside Facility field? will that be constant always?

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-14 : 10:49:13

there's currently only 3 values within the facility.name field. So for the time being I've modified my query to give me those three results. But if the company grows and adds 10 more facilities then I'll need to go into the query and add 10 more subqueries. I don't see that happening so I'm content with doing it manually for now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:58:30

ok then current query will be fine. something like:-


SELECT     Class.Name, COUNT(CASE WHEN Facility.Name = 'Facility1' AND classID IS NOT NULL 
                      THEN Member.ID ELSE NULL END) AS [Facility1],
 COUNT(CASE WHEN Facility.Name = 'Facility2' AND classID IS NOT NULL 
                      THEN Member.ID ELSE NULL END) AS [Facility2],
 COUNT(CASE WHEN Facility.Name = 'Facility3' AND classID IS NOT NULL 
                      THEN Member.ID ELSE NULL END) AS [Facility3]
FROM         Member INNER JOIN
                      Contracts ON Member.ContractID = Contracts.ContractID INNER JOIN
                      Facility ON Member.FacilityID = Facility.FacilityID INNER JOIN
                      Class ON Contracts.ClassId = Class.ClassID LEFT OUTER JOIN
                      [#PrimaryClasses] ON classID = Contracts.ClassID
WHERE     (Facility.Name <> 'Corporate') AND (Member.Category <> 'C') and classid is not null
GROUP BY Class.Name

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-14 : 11:04:20

Thanks visakh16! Yeah, that's what I had done and it works perfect. It's not dynamic like I mentioned but it's done and working and I can now move on to the next issue rather then spending a ton of time on figuring out how to create the dynamic crosstab stuff =)

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 11:42:51

welcome

jmill130
Starting Member

26 Posts

Posted - 2009-05-14 : 13:10:19

Check out sp_crosstab stored procedure

Example of code:

use casemix execute sp_Crosstab
'Select Patcom, ChgBucket32FieldName , BillCharge, PatientFlag from [BayChgBucket32temp_view]',
NULL,
NULL,
'ChgBucket32FieldName',
'BillCharge',
'SUM',
NULL,
1,
'BayCurrentChgBucket32temp'

syntax
- select statement
- where statement
- column headings to force order and correct number of headings
- field that is column heading
- field to summarize
- summary method (COUNT, SUM, MIN, MAX or AVG)
- column name for null values
- 1 = one additional column for overall sum
- target table name
- database or null for current
- 0 to overwrite table, 1 to append

I can send you the sp_crosstab code you need to create the stored procedure. Its fairly easy to use and I am not a SQL guru.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-14 : 22:31:16

also see these
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources