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
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
You may be able to use Pivot tables to achieve the result too
