Displaying the multiple rows data in single Row in SQL

 You have a scenario to show the multiple rows data into the  single rows like below.

In Above image you can see how the  multiple rows of above tables data is transformed into horzotally and make all the records into one row.

Just take the example of 1 data and taking it is making the horizontally as respect to the TIN wise.

where for TIN 1000 having the four different records but belongs to the one costumer only.

So first provide the row number to all the records like below.

SELECT SetId, AppCode, AppEventId, EventId,ValueData,phone,[Address], ROW_NUMBER()  OVER( PARTITION BY  AppCode  ORDER BY AppCode

) RowNumber

INTo #mineTable

FROM  dbo.valueDataCheck

In Above Code there we created a new temp table which will maintain the row number with other records and you can see in below line how we provide numbers to all the records

select * from #mineTable

Now main code how we will convert the row to horizontally like given in screen.

SELECT SetId, AppCode as TIN, AppEventId, EventId

  ,max(CASE WHEN RowNumber = 1 THEN ValueData END) AS val1

  ,max(CASE WHEN RowNumber = 2 THEN ValueData END) AS val2

  ,max(CASE WHEN RowNumber = 3 THEN ValueData END) AS val3

   --,max(CASE WHEN RowNumber = 4 THEN ValueData END) AS val4

   ,max(CASE WHEN RowNumber = 1 THEN phone END) AS phonenumber1

  ,max(CASE WHEN RowNumber = 2 THEN phone END) AS phonenumber2

  ,max(CASE WHEN RowNumber = 3 THEN phone END) AS phonenumber3

  -- ,max(CASE WHEN RowNumber = 4 THEN phone END) AS phonenumber4

   ,max(CASE WHEN RowNumber = 1 THEN [Address] END) AS add1

  ,max(CASE WHEN RowNumber = 2 THEN [Address] END) AS add2

  ,max(CASE WHEN RowNumber = 3 THEN [Address] END) AS add3

 -- ,max(CASE WHEN RowNumber = 4 THEN [Address] END) AS add4

 

   FROM #mineTable

   GROUP BY SetId,AppCode,AppEventId,EventId

  

   DROP TABLE #mineTable

It will help you to get the all the records horizontally . Dont forget to drop tables.