Transpose Data - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Transpose Data

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2002-07-18 : 10:54:32

Is there a function in SQL to transpose my data?

Here is what my table look like

Storeid int,
departementId int,
amount money

1 1 30
1 2 34
1 3 56
2 1 25
2 2 28
2 3 52

I'd like to make a query to have my data like this

storeId, department1, department2,department3
1 30 34 56
2 25 28 52

For now I'm using
select case when department = 1 then amount,
case when department = 2 then amount

is there any function in T-SQL to make this quicker

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 11:04:47

Yes, you can use the CASE statement to cross-tab your data:

SELECT StoreID,
Max(CASE DepartmentID WHEN 1 THEN DepartmentID END) AS Department1,
Max(CASE DepartmentID WHEN 2 THEN DepartmentID END) AS Department2,
Max(CASE DepartmentID WHEN 3 THEN DepartmentID END) AS Department3
FROM myTable
GROUP BY StoreID

If you only have one value for each category, you can use either Max or Min as your aggregate function. If you want to sum or average them, then use SUM() or AVG() instead.

If you have dynamically-changing cross tab columns, take a look here:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=cross+tab

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-19 : 10:42:13

Hi

I have the same sort of question. So I thought just adding it here.

This is the table I'm sitting with.

AGE Unit TW SS TR QR ADD_ADULT1
AD 0 898 578 1243 1796 218
CH 164 0 0 0 0 0
IN 109 0 0 0 0 0

In our database the child and infant rate is loaded under a unit column.

I'm trying to pull the rates direct from the database but need a child and infant column next to the other columns. Something like this

AGE TW SS TR QR ADD_ADULT1 CH IN
AD 898 578 1243 1796 218 164 109

Is this possible??

Thx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 10:46:29

quote:
Originally posted by ewaldbruwer

Hi

I have the same sort of question. So I thought just adding it here.

This is the table I'm sitting with.

AGE Unit TW SS TR QR ADD_ADULT1
AD 0 898 578 1243 1796 218
CH 164 0 0 0 0 0
IN 109 0 0 0 0 0

In our database the child and infant rate is loaded under a unit column.

I'm trying to pull the rates direct from the database but need a child and infant column next to the other columns. Something like this

AGE TW SS TR QR ADD_ADULT1 CH IN
AD 898 578 1243 1796 218 164 109

Is this possible??

Thx in advance

Do you have some other columns in your table?

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-19 : 11:04:18

Is there anything in specific that is needed extra to make this easier to do??

A column with a value that is the same for all three lines??

My Transact is limited. And trying to do a miracle in my books:-)

Thx for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 11:12:13

quote:
Originally posted by ewaldbruwer

Is there anything in specific that is needed extra to make this easier to do??

A column with a value that is the same for all three lines??

My Transact is limited. And trying to do a miracle in my books:-)
Thx for the help

How would you decide which CH & IN rows should be merged to which AD row? or will you always have only 3 rows in your table?

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-20 : 05:47:43

To answer your question. Yes there will always be AD, CH and IN in three lines.

What I thought of doing was to exclude AD from the list. Then I have another column with the same number in on all three lines. Which would be a link for the data. Then in a way use that number as a link to move the CH and IN into separate columns.

All of this is not in one table. I'm linking about 5 different tables to get all the info I need.

Hope this make sense.

Example:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 05:55:44

quote:
Originally posted by ewaldbruwer

To answer your question. Yes there will always be AD, CH and IN in three lines.

What I thought of doing was to exclude AD from the list. Then I have another column with the same number in on all three lines. Which would be a link for the data. Then in a way use that number as a link to move the CH and IN into separate columns.

All of this is not in one table. I'm linking about 5 different tables to get all the info I need.

Hope this make sense.

Example:

SELECT MAX(CASE WHEN AGE='AD' THEN AGE ELSE NULL END) AS AGE,
MAX(CASE WHEN AGE='AD' THEN TW ELSE NULL END)AS TW,
MAX(CASE WHEN AGE='AD' THEN SS ELSE NULL END) AS SS,
...all other fields for AD row
MAX(CASE WHEN AGE='CH' THEN Unit ELSE NULL END) AS CH,
MAX(CASE WHEN AGE='IN' THEN Unit ELSE NULL END) AS IN
FROM YourTable
GROUP BY OSR_ID

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-20 : 08:36:04

THX a million!!! Got it to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:24:55

quote:
Originally posted by ewaldbruwer

THX a million!!! Got it to work.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources