CODEWITHSUNDEEP
sql-server-2008t-sqlms-accesspivot
Mohamed Kamal
Mohamed Kamal

Reputation: 2352

write a TRANSFORM statement in Sql Server

I am migrating a web application backend from Access to MSSQL, however I was not able o reproduce the following Query in MSSQL, any ideas?

TRANSFORM First(FollowUp.FUData) AS FirstOfFUData
SELECT FollowUp.MRN
FROM FollowUp
GROUP BY FollowUp.MRN
PIVOT FollowUp.FU;

please note that this query converts data from the EAV table Followup to a normal table. This is the design of the table Followup:

enter image description here

Upvotes: 1

Views: 13074

Answers (1)

Taryn
Taryn

Reputation: 247700

In SQL Server you can use the PIVOT function and your query would be set up this way:

select MRN, Value1, Value2
from
(
  select MRN, FUData, FU
  from FollowUp
) src
pivot
(
  max(FUData)
  for FU in (Value1, Value2)
) piv

Where you would replace the Value1, Value2, etc with any of the values that you items that should now be columns.

SQL Server 2008, does not have a FIRST() function so you will have to use another aggregate function or query the data in such a manner to return the the first record for each item in FU.

Another way to write this is using an aggregate function with a CASE statement:

select MRN, 
  max(case when FU = 'value1' then FUData else null end) Value1,
  max(case when FU = 'value2' then FUData else null end) Value2
from FollowUp
group by MRN

The above versions will work great if you have a known number of FU values to transform into columns, but if you do not then you will need to use dynamic SQL similar to this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FU) 
                    from FollowUp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT MRN,' + @cols + ' from 
             (
                select MRN, FUData, FU
                from FollowUp
            ) x
            pivot 
            (
                max(FUData)
                for FU in (' + @cols + ')
            ) p '

execute(@query)

Upvotes: 6

Related Questions