Stored procedure from multiple rows into multiple columns

Question

This is my current data:

GID, AppID, GName, GDesig, GBusinessName
101   3002   A     JO       AB House
102   3002   B     EO       BD Ltd

I need to mention that the number of rows may be 1 to 4. I want to show this record like this:

GID, AppID, GName, GDesig, GBusinessName, GName, GDesig, GBusinessName
101   3002   A     JO       AB House      B      EO       BD Ltd

How can I do this with a stored procedure? If any one give me any solution, that will be helpful for me.

Thanks

Nur

IS the GID in the second set from the first i.e 101? Do you just want to lose the other GIDs i.e. 102, 103 or 104 in the result set? — , Oct 01 '13 at 18:18
I want just these column and these are enough for me :: AppID, GName, GDesig, GBusinessName, GName, GDesig, GBusinessName — Nur Muhammad, Oct 01 '13 at 18:23

score 1 · Accepted Answer · answered Oct 01 '13 at 18:41

You did not mention what should be unique to each row in the output. I assumed it should be AppId. Given that, the trick is the Partition By where you rank each row for each grouping of AppId. What you seek is a crosstab. Given that you said there can be up to four rows for each group, we can build a static crosstab from that.

;With RnkItems As
    (
    Select GID, AppId, GName, GDesig, GBusinessName
        , Row_Number() Over ( Partition By AppId Order By GID, GName ) As Rnk
    From SourceData
    )
Select AppId
    , Min( Case When Rnk = 1 Then GName End ) As GName1
    , Min( Case When Rnk = 1 Then GDesig End ) As GDesig1
    , Min( Case When Rnk = 1 Then GBusinessName End ) As GBusinessName1

    , Min( Case When Rnk = 2 Then GName End ) As GName2
    , Min( Case When Rnk = 2 Then GDesig End ) As GDesig2
    , Min( Case When Rnk = 2 Then GBusinessName End ) As GBusinessName2

    , Min( Case When Rnk = 3 Then GName End ) As GName3
    , Min( Case When Rnk = 3 Then GDesig End ) As GDesig3
    , Min( Case When Rnk = 3 Then GBusinessName End ) As GBusinessName3

    , Min( Case When Rnk = 4 Then GName End ) As GName4
    , Min( Case When Rnk = 4 Then GDesig End ) As GDesig4
    , Min( Case When Rnk = 4 Then GBusinessName End ) As GBusinessName4
From RnkItems
Group By AppId

SQL Fiddle version

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

You may be able to use Pivot tables to achieve the result too

How to create a PivotTable in Transact/SQL?

edited May 23 '17 at 10:32

Community

1
1

answered Oct 01 '13 at 19:02

Alaa Awad

3,612
6
25
35

Stored procedure from multiple rows into multiple columns

2 Answers2