ekb0211


I wasn't sure what to put for the subject, because it involves a little more that what's stated. I need a little help with a query, and I hope I'm not making this way more complicated that it needs to be, but here goes: I'm trying to put together a table that tells me whether particular items are taxable for a particular state and a particular customer. So, I will give my stored procedure a customer key, and it will give me back a table listing each State (rows), and data telling me whether or not each item class from another table is taxable or not, using the ItemClassNames from the ItemClass table as the Column names.

I have three tables: State, ItemClass and ItemClassTax. Here they are:

State
StateKey(PK) StateName
1 Alabama
2 Alaska
3 Arizona
.. ...
50 Wyoming

ItemClass Table
ItemClassKey(PK) ItemClassName
1 ItemClassName1
2 ItemClassName2
3 ItemClassName3
4 ItemClassName4
... ....

And the third table. Every item that is taxable for a particular state and particular customer will have a record in this table, ItemClassTax. If an item class is not taxable, it will not have a record in this table.

ItemClassTax Table
ItemClassTaxKey(PK) ItemClassKey(FK) StateKey(FK) CustomerKey(FK)


Here is what I'm looking to get in the end.

StateKey ItemClassName1 ItemClassName2 ItemClassName3 ....
1 record exists (taxable) null (not taxable) record exists (taxable)
2 null (not taxable) null (not taxable) record exists (taxable)
3 record exists (taxable) record exists (taxable) record exists (taxable)
4 null (not taxable) null (not taxable) null (not taxable)
... ... ... ...
50 null (not taxable) record exists (taxable) record exists (taxable)

I would really like the table to end as shown above, and preferably using booleans to state whether or not the record exists or not, because I want to easily bind my results to a control in my C# windows form. Is this query at all possible I would really appreciate any help to get me going in the right direction. Thanks in advance! and sorry that this is so long!




Re: Query Help Please: Use Data in one table as Column names in resulting table.

Janos Berke


Hi,

Maybe, you should try the "PIVOT" T-SQL operator.

Regards,

Janos







Re: Query Help Please: Use Data in one table as Column names in resulting table.

Manivannan.D.Sekaran

Here you go..

Code Snippet

Create Table #state (

[StateKey] int ,

[StateName] Varchar(100)

);

Insert Into #state Values('1','Alabama');

Insert Into #state Values('2','Alaska');

Insert Into #state Values('3','Arizona');

Insert Into #state Values('4','Texas');

Insert Into #state Values('50','Wyoming');

Create Table #itemclass (

[ItemClassKey] int ,

[ItemClassName] Varchar(100)

);

Insert Into #itemclass Values('1','ItemClassName1');

Insert Into #itemclass Values('2','ItemClassName2');

Insert Into #itemclass Values('3','ItemClassName3');

Insert Into #itemclass Values('4','ItemClassName4');

Create Table #itemclasstax (

[ItemClassTaxKey] int ,

[StateKey] int ,

[ItemClassKey] int

);

Insert Into #itemclasstax Values('1','1','1');

Insert Into #itemclasstax Values('2','1','3');

Insert Into #itemclasstax Values('3','2','3');

Insert Into #itemclasstax Values('4','3','1');

Insert Into #itemclasstax Values('5','3','2');

Insert Into #itemclasstax Values('5','3','3');

Insert Into #itemclasstax Values('6','50','2');

Insert Into #itemclasstax Values('6','50','3');

--For SQL Server 2000 & 2005

Select

S.StateKey

, Max(Case When ICS.ItemClassKey=1 Then IC.ItemClassName End) as ItemClassName1

, Max(Case When ICS.ItemClassKey=2 Then IC.ItemClassName End) as ItemClassName2

, Max(Case When ICS.ItemClassKey=3 Then IC.ItemClassName End) as ItemClassName3

, Max(Case When ICS.ItemClassKey=4 Then IC.ItemClassName End) as ItemClassName4

from

#itemclasstax ICS

Join #itemclass IC on IC.ItemClassKey = ICS.ItemClassKey

Right Outer Join #state S on S.StateKey = ICS.StateKey

Group By

S.StateKey

--For SQL Server 2005 ONLY

;With CTE

as

(

Select

S.StateKey

, IC.ItemClassKey

, IC.ItemClassName

from

#itemclasstax ICS

Join #itemclass IC on IC.ItemClassKey = ICS.ItemClassKey

Right Outer Join #state S on S.StateKey = ICS.StateKey

)

Select * from CTE

Pivot

(

Max(ItemClassName) For ItemClassKey in ([1],[2],[3],[4])

)as PVT







Re: Query Help Please: Use Data in one table as Column names in resulting table.

ekb0211

Manivannan.D.Sekaran wrote:

Here you go..

Code Snippet

Create Table #state (

[StateKey] int ,

[StateName] Varchar(100)

);

Insert Into #state Values('1','Alabama');

Insert Into #state Values('2','Alaska');

Insert Into #state Values('3','Arizona');

Insert Into #state Values('4','Texas');

Insert Into #state Values('50','Wyoming');

Create Table #itemclass (

[ItemClassKey] int ,

[ItemClassName] Varchar(100)

);

Insert Into #itemclass Values('1','ItemClassName1');

Insert Into #itemclass Values('2','ItemClassName2');

Insert Into #itemclass Values('3','ItemClassName3');

Insert Into #itemclass Values('4','ItemClassName4');

Create Table #itemclasstax (

[ItemClassTaxKey] int ,

[StateKey] int ,

[ItemClassKey] int

);

Insert Into #itemclasstax Values('1','1','1');

Insert Into #itemclasstax Values('2','1','3');

Insert Into #itemclasstax Values('3','2','3');

Insert Into #itemclasstax Values('4','3','1');

Insert Into #itemclasstax Values('5','3','2');

Insert Into #itemclasstax Values('5','3','3');

Insert Into #itemclasstax Values('6','50','2');

Insert Into #itemclasstax Values('6','50','3');

--For SQL Server 2000 & 2005

Select

S.StateKey

, Max(Case When ICS.ItemClassKey=1 Then IC.ItemClassName End) as ItemClassName1

, Max(Case When ICS.ItemClassKey=2 Then IC.ItemClassName End) as ItemClassName2

, Max(Case When ICS.ItemClassKey=3 Then IC.ItemClassName End) as ItemClassName3

, Max(Case When ICS.ItemClassKey=4 Then IC.ItemClassName End) as ItemClassName4

from

#itemclasstax ICS

Join #itemclass IC on IC.ItemClassKey = ICS.ItemClassKey

Right Outer Join #state S on S.StateKey = ICS.StateKey

Group By

S.StateKey

--For SQL Server 2005 ONLY

;With CTE

as

(

Select

S.StateKey

, IC.ItemClassKey

, IC.ItemClassName

from

#itemclasstax ICS

Join #itemclass IC on IC.ItemClassKey = ICS.ItemClassKey

Right Outer Join #state S on S.StateKey = ICS.StateKey

)

Select * from CTE

Pivot

(

Max(ItemClassName) For ItemClassKey in ([1],[2],[3],[4])

)as PVT



Thanks so much! I think this will help me a lot - the query does what I need it to. I will have to use the first example of yours, as I forgot to mention that I am using SQL Server 2000.

The only concern I have, is that the records in the ItemClass table will be dynamic - new records will be added to that table all the time - so I can't so I can't just select each column name and give it a static name (ex. ItemClassName1, ItemClassName2, etc - see underlined fields below), unless I wanted to go and change the query everytime records were added to the ItemClass table. I'd like to get the actual ItemClassName record from the ItemClass table to use as the column names.

Select

S.StateKey

, Max(Case When ICS.ItemClassKey=1 Then IC.ItemClassName End) as ItemClassName1

, Max(Case When ICS.ItemClassKey=2 Then IC.ItemClassName End) as ItemClassName2

, Max(Case When ICS.ItemClassKey=3 Then IC.ItemClassName End) as ItemClassName3

, Max(Case When ICS.ItemClassKey=4 Then IC.ItemClassName End) as ItemClassName4


I appreciate the time you've taken to help me out! Thanks again!





Re: Query Help Please: Use Data in one table as Column names in resulting table.

ekb0211

Sorry, I'm the original poster, but I changed my display name from maguidhir to ekb0211.