Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-19-2008
01:25 PM

05-19-2008
01:25 PM

Transpose using Dynamic SQL

Matrix is to be created using aggregate values of different Expenseid's for the employees.

Matrix table

Empl Exp1 Exp2 Exp3 Exp4

1 1000 2000 1200 2300

2 3212 1800 900 1850

Source table looks like

Empid ExpID Amt

1 1 300

1 2 400

1 1 100

1 2 200

1 1 300

1 2 800

1 1 300

1 2 600

Intermediate table has unique ExpID.

Can anyone help me in getting the output as desired using dynamic sql or anyother method?

Matrix table

Empl Exp1 Exp2 Exp3 Exp4

1 1000 2000 1200 2300

2 3212 1800 900 1850

Source table looks like

Empid ExpID Amt

1 1 300

1 2 400

1 1 100

1 2 200

1 1 300

1 2 800

1 1 300

1 2 600

Intermediate table has unique ExpID.

Can anyone help me in getting the output as desired using dynamic sql or anyother method?

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-20-2008
10:54 AM

05-20-2008
10:54 AM

Re: Transpose using Dynamic SQL

Two ways to answer your problem :

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-20-2008
10:57 AM

05-20-2008
10:57 AM

Re: Transpose using Dynamic SQL

Two ways to answer your problem :

select empid

, sum(exp1) as exp1

, sum(exp2) as exp2

, sum(exp3) as exp3

, sum(exp4) as exp4

from

( select empid

, sum(amt) as exp1

, 0 as exp2

, 0 as exp3

, 0 as exp4

from source

where expid = 1

group by empid

union

select empid

, 0 as exp1

, sum(amt) as exp2

, 0 as exp3

, 0 as exp4

from source

where expid = 2

group by empid

...

) as tmp

group by tmp

;

select empid

, sum(case expid when 1 then amt end) as exp1

, sum(case expid when 2 then amt end) as exp2

, sum(case expid when 3 then amt end) as exp3

, sum(case expid when 4 then amt end) as exp4

from source

group by empid

;

select empid

, sum(exp1) as exp1

, sum(exp2) as exp2

, sum(exp3) as exp3

, sum(exp4) as exp4

from

( select empid

, sum(amt) as exp1

, 0 as exp2

, 0 as exp3

, 0 as exp4

from source

where expid = 1

group by empid

union

select empid

, 0 as exp1

, sum(amt) as exp2

, 0 as exp3

, 0 as exp4

from source

where expid = 2

group by empid

...

) as tmp

group by tmp

;

select empid

, sum(case expid when 1 then amt end) as exp1

, sum(case expid when 2 then amt end) as exp2

, sum(case expid when 3 then amt end) as exp3

, sum(case expid when 4 then amt end) as exp4

from source

group by empid

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-21-2008
02:15 AM

05-21-2008
02:15 AM

Re: Transpose using Dynamic SQL

That is a valid answer.

What if my expense id keeps changing, but I have a reference table which holds all the expense and I need to aggregate dynamically?

What if my expense id keeps changing, but I have a reference table which holds all the expense and I need to aggregate dynamically?