question

ASHISHSINGH-6178 avatar image
0 Votes"
ASHISHSINGH-6178 asked MelissaMa-msft commented

TSQL - need output like pivot

Hi All, In my sql table, I have a results like this ..

117202-excel-2.jpg


but in requirement, i need output in this way ..

117089-image.png


sql-server-transact-sql
excel-2.jpg (21.1 KiB)
image.png (4.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ASHISHSINGH-6178,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

The next query should work for your problem formulation:

 select [Identity No] as [medical_claim_service_line_id], 
     isnull([1], '') as [Order_code_1], 
     isnull([2], '') as [Order_code_2], 
     isnull([3], '') as [Order_code_3], 
     isnull([4], '') as [Order_code_4], 
     isnull([5], '') as [Order_code_5], 
     isnull([6], '') as [Order_code_6]
 from MyTable
 pivot ( max([Order Code]) for [order] in ([1], [2], [3], [4], [5], [6])) p

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ASHISHSINGH-6178,

Welcome to Microsoft Q&A!

Please also refer below:

 create table yourtable 
 ([Identity NO] varchar(20),
 [order] int,
 [Order Code] varchar(10))
    
 insert into yourtable values
 ('XXXXXX0559148',5,'I776'),
 ('XXXXXX0559148',6,'E039'),
 ('XXXXXX0559148',3,'E785'),
 ('XXXXXX0559148',1,'J9600'),
 ('XXXXXX0559148',2,'Z23')

  select [Identity No] as [medical_claim_service_line_id], 
      max(case when [order]=1 then  [Order Code] end) as [Order_code_1], 
      max(case when [order]=2 then  [Order Code] end) as [Order_code_2], 
      max(case when [order]=3 then  [Order Code] end) as [Order_code_3], 
      max(case when [order]=4 then  [Order Code] end) as [Order_code_4], 
      max(case when [order]=5 then  [Order Code] end) as [Order_code_5], 
      max(case when [order]=6 then  [Order Code] end) as [Order_code_6]
  from yourtable
  group by [Identity No]

Output:

117204-output.png

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output.png (3.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.