June 7, 2013 at 12:57 pm
Hi, I have a table where I have amount and rank columns.
It is basically an approval workflow. there can be several approvers for one area/unit where the level is he rank.
I need to know a way of getting one column for each approving level.
For example, for the CID 25756Q, I need to display the approvers as shown below.
CIDID1Amt1ID2Amt2ID3Amt3ID4Amt4ID5Amt5ID6Amt6ID7Amt7
25756Q12345A0.112345B0.212345C0.312345D0.412345E0.512345F0.612345G0.7
This is the DDL
Drop table ApproverT
CREATE TABLE [dbo].[ApproverT](
[CID] [varchar](6) NULL,
[ID] [char](6) NULL,
[Amount] [real] NULL,
[Rank] [int] NULL)
Insert into ApproverT
(CID, ID, Amount, Rank)
Select '25756Q', '12345A', 0.10, 1 UNION ALL
Select '25756Q', '12345B', 0.20, 2 UNION ALL
Select '25756Q', '12345C', 0.30, 3 UNION ALL
Select '25756Q', '12345D', 0.40, 4 UNION ALL
Select '25756Q', '12345E', 0.50, 5 UNION ALL
Select '25756Q', '12345F', 0.60, 6 UNION ALL
Select '25756Q', '12345G', 0.70, 7 UNION ALL
Select '93512X', '123456', 0.01, 1 UNION ALL
Select '91142A', '654321', 0.10, 1 UNION ALL
Select '91142A', '963852', 1.00, 2
I will appreciate any help on this.
Thanks,
June 7, 2013 at 1:14 pm
You need a crosstab for this. If you know the max number of columns you can use a static crosstab. If you don't know ahead of time how many there might be you will need to use a dynamic version. You can an article for both types of crosstabs in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 1:21 pm
Here is how you could do the static one.
select CID,
MAX(case when Rank = 1 then ID end) as ID1,
MAX(case when Rank = 1 then Amount end) as Amount1,
MAX(case when Rank = 2 then ID end) as ID2,
MAX(case when Rank = 2 then Amount end) as Amount2,
MAX(case when Rank = 3 then ID end) as ID3,
MAX(case when Rank = 3 then Amount end) as Amount3,
MAX(case when Rank = 4 then ID end) as ID4,
MAX(case when Rank = 4 then Amount end) as Amount4,
MAX(case when Rank = 5 then ID end) as ID5,
MAX(case when Rank = 5 then Amount end) as Amount5,
MAX(case when Rank = 6 then ID end) as ID6,
MAX(case when Rank = 6 then Amount end) as Amount6,
MAX(case when Rank = 7 then ID end) as ID7,
MAX(case when Rank = 7 then Amount end) as Amount7
from ApproverT
group by CID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 1:27 pm
yeah, this works.
Thanks,
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply