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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy