SQL Server Pivoting the table rows to colums

ff29svar 于 2023-06-28 发布在其他

关注(0)|答案(2)|浏览(141)

My table is as follows:

declare @tab TABLE (Id int, Rid INT, FValue VARCHAR(50), FText VARCHAR(50))

INSERT INTO @tab VALUES
   (1,1,'Patient','P1'),
   (1,1,'Sdate','01/06/2023'),
   (1,1,'Acc#','123265'),
   (1,1,'Status','New'),
   (1,2,'Patient','P2'),
   (1,2,'Sdate','02/06/2023'),
   (1,2,'Acc#','512364'),
   (1,2,'Status','New'),
   (1,3,'Patient','P3'),
   (1,3,'Sdate','03/06/2023'),
   (1,3,'Acc#','632154'),
   (1,3,'Status','Complete')

select * from @tab

I tried pivoting it, using the following code, but without any success.

select RNo, Patient, Sdate, Acc#,[Status]
from
(
  select Rid, FValue, FText
  from @tab
) d
pivot
(
  max(Rid) for FText in (Rno, Patient, Sdate, Acc#,[Status])
) piv;

Output table should be:

declare @tab1 TABLE (Rno int, Patient VARCHAR(50), Sdate Datetime, Acc# VARCHAR(50), [Status] VARCHAR(50))

INSERT INTO @tab1 
VALUES
   (1,'P1','01/06/2023','123265','New'), 
   (2,'P2','02/06/2023','512364','New'), 
   (3,'P3','03/06/2023','632154','Complete')

select * from @tab1

sql-server

来源：https://stackoverflow.com/questions/76537295/pivoting-the-table-rows-to-colums

2条答案

按热度按时间

jckbn6z71#

It's a simple PIVOT , which turns the unique values from the FValue column into multiple columns:

SELECT *
FROM (
   SELECT Id, Rid, FValue, FText
   FROM @tab
) t
PIVOT (
   MAX(FText) FOR FValue IN ([Patient], [Sdate], [Acc#], [Status])
) p

赞(0）回复(0）举报 2023-06-28

mkshixfv2#

Updated Query

SELECT RNo, Patient, Sdate, [Acc#], [Status]
    FROM (
      SELECT Rid, FValue, FText
      FROM @tab
    ) AS src
    PIVOT (
      MAX(FValue)
      FOR FText IN (RNo, Patient, Sdate, [Acc#], [Status])
    ) AS piv;

The FOR FText in clause of the PIVOT statement should contain the column names from the FText column of your @tab table. The column names should be enclosed in square brackets if they contain special characters like "#" or spaces.

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server Pivoting the table rows to colums

2条答案

相关问题

热门标签

最新问答