Manipulating output of temp table using sql

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 2005 Forums

Transact-SQL (2005)

Manipulating output of temp table using sql

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2010-06-24 : 11:19:03

Hi,
I have a temporary table output which is in the format below.
site cust_num slsman sales_ytd sales_lastyr
11121 C000200 904 0 0
11199 C000200 904 1023.1 0
11099 C000200 904 0 0
11111 C000200 904 343.5 25
11112 C000200 904 0 0
11103 C000201 927 2000.12 3000
11101 C000201 927 325.12 100.33
11199 C000201 927 0 0
11121 C000201 927 245.5 0

However I need to come up with a sql manipulation which will transform the above in the following format:

cust_num slsman 11121_ytd 11121_lastyr 11199_ytd 11199_lastyr 11099_ytd 11099_lastyr 11111_ytd 11111_lastyr 11112_ytd 11112_lastyr 11103_ytd 11103_lastyr 11101_ytd 11101_lastyr 11199_ytd 11199_lastyr
c000200 904 0 0 1023.1 0 0 0 343.5 25 0 0
c000201 904 245 0 2000.12 3000 325.12 100.33 0 0

I have not idea how to do this. I appreciate any help for this resolution. Thanks.

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 16:49:33

If there are lots of sites then research Pivot tables.
Otherwise you could just do:

select cust_num,slsman,
sum(case when site = 11121 then sales_ytd else 0 end) as 11121_ytd,
sum(case when site = 11121 then sales_lastyr else 0 end) as 11121_lastyr,...(And so on for each site)
group by cust_num,slsman

Pete

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 16:59:51

Use [ code] [ /code] tags without the leading space

quote:
Originally posted by gamaz

Hi,
I have a temporary table output which is in the format below.

site cust_num slsman sales_ytd sales_lastyr
11121 C000200 904 0 0
11199 C000200 904 1023.1 0
11099 C000200 904 0 0
11111 C000200 904 343.5 25
11112 C000200 904 0 0
11103 C000201 927 2000.12 3000
11101 C000201 927 325.12 100.33
11199 C000201 927 0 0
11121 C000201 927 245.5 0

However I need to come up with a sql manipulation which will transform the above in the following format:

cust_num slsman 11121_ytd 11121_lastyr 11199_ytd 11199_lastyr 11099_ytd 11099_lastyr 11111_ytd 11111_lastyr 11112_ytd 11112_lastyr 11103_ytd 11103_lastyr 11101_ytd 11101_lastyr 11199_ytd 11199_lastyr
c000200 904 0 0 1023.1 0 0 0 343.5 25 0 0
c000201 904 245 0 2000.12 3000 325.12 100.33 0 0

I have not idea how to do this. I appreciate any help for this resolution. Thanks.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 17:02:15

Yes but the real question is...

"How many turtles does it go down?"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources