CODEWITHSUNDEEP
sql-server-2008
Nur Muhammad
Nur Muhammad

Reputation: 111

Stored procedure from multiple rows into multiple columns

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

Upvotes: 1

Views: 111

Answers (2)

Alaa Awad
Alaa Awad

Reputation: 4152

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

How to create a PivotTable in Transact/SQL?

Upvotes: 0

Thomas
Thomas

Reputation: 64645

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

Upvotes: 1

Related Questions