SELECTing Rows to Columns - How to Get Column Alias From T..

Considering the following query which transposes rows into columns as
desired; please notice that the column name aliases are "hard coded" (e.g.,
AS ABO, AS RHFactor, AS A1, AS A2, etc).

Each row in the table Lookup_TransplantParticipantFacts contains the desired
"column name" for the corresponding FactID value. The row where FactID = 1
has FactName = 'ABO'. Rather than hard coding "AS ABO" (etc) in the SELECT
list, I'd like to somehow return that value ("ABO") - as the column alias in
the result set - from the underlying table. Is there any way I can do that?

SELECT
T.TransplantEventID,
T.DonorID,
MAX(CASE WHEN L.FactID = 1 THEN T.FactValue END) AS ABO,
MAX(CASE WHEN L.FactID = 2 THEN T.FactValue END) AS RHFactor,
MAX(CASE WHEN L.FactID = 3 THEN T.FactValue END) AS A1,
MAX(CASE WHEN L.FactID = 4 THEN T.FactValue END) AS A2,
MAX(CASE WHEN L.FactID = 5 THEN T.FactValue END) AS B1,
MAX(CASE WHEN L.FactID = 6 THEN T.FactValue END) AS B2,
MAX(CASE WHEN L.FactID = 7 THEN T.FactValue END) AS DR1,
MAX(CASE WHEN L.FactID = 8 THEN T.FactValue END) AS DR2
FROM
TransplantEventsXDonorFacts T INNER JOIN
Lookup_TransplantParticipantFacts L ON T.FactID = L.FactID
GROUP BY
T.TransplantEventID,
T.DonorID

-----------------------------------
DDL For Relevant Tables Below:
-----------------------------------

CREATE TABLE Lookup_TransplantParticipantFacts(
FactID int IDENTITY(1,1) NOT NULL,
FactName varchar (30) NOT NULL,
Comments varchar (500) NULL
CONSTRAINT PK_Lookup_TransplantParticipantFacts PRIMARY KEY CLUSTERED
(FactID ASC)
....

CREATE TABLE TransplantEventsXDonorFacts(
TransplantEventID int NOT NULL,
DonorID int NOT NULL,
FactID int NOT NULL,
FactValue varchar(300) NULL,
CONSTRAINT PK_TransplantEventsXDonorFacts PRIMARY KEY CLUSTERED
(
TransplantEventID ASC,
DonorID ASC,
FactID ASC
....


Thanks!

if u r using sqlserver2005 use PIVOT / UNPIVOT Operator

"Bob Johnson" wrote:

> Considering the following query which transposes rows into columns as
> desired; please notice that the column name aliases are "hard coded" (e.g.,
> AS ABO, AS RHFactor, AS A1, AS A2, etc).
>
> Each row in the table Lookup_TransplantParticipantFacts contains the desired
> "column name" for the corresponding FactID value. The row where FactID = 1
> has FactName = 'ABO'. Rather than hard coding "AS ABO" (etc) in the SELECT
> list, I'd like to somehow return that value ("ABO") - as the column alias in
> the result set - from the underlying table. Is there any way I can do that?
>
> SELECT
> T.TransplantEventID,
> T.DonorID,
> MAX(CASE WHEN L.FactID = 1 THEN T.FactValue END) AS ABO,
> MAX(CASE WHEN L.FactID = 2 THEN T.FactValue END) AS RHFactor,
> MAX(CASE WHEN L.FactID = 3 THEN T.FactValue END) AS A1,
> MAX(CASE WHEN L.FactID = 4 THEN T.FactValue END) AS A2,
> MAX(CASE WHEN L.FactID = 5 THEN T.FactValue END) AS B1,
> MAX(CASE WHEN L.FactID = 6 THEN T.FactValue END) AS B2,
> MAX(CASE WHEN L.FactID = 7 THEN T.FactValue END) AS DR1,
> MAX(CASE WHEN L.FactID = 8 THEN T.FactValue END) AS DR2
> FROM
> TransplantEventsXDonorFacts T INNER JOIN
> Lookup_TransplantParticipantFacts L ON T.FactID = L.FactID
> GROUP BY
> T.TransplantEventID,
> T.DonorID
>
> -----------------------------------
> DDL For Relevant Tables Below:
> -----------------------------------
>
> CREATE TABLE Lookup_TransplantParticipantFacts(
> FactID int IDENTITY(1,1) NOT NULL,
> FactName varchar (30) NOT NULL,
> Comments varchar (500) NULL
> CONSTRAINT PK_Lookup_TransplantParticipantFacts PRIMARY KEY CLUSTERED
> (FactID ASC)
> ....
>
> CREATE TABLE TransplantEventsXDonorFacts(
> TransplantEventID int NOT NULL,
> DonorID int NOT NULL,
> FactID int NOT NULL,
> FactValue varchar(300) NULL,
> CONSTRAINT PK_TransplantEventsXDonorFacts PRIMARY KEY CLUSTERED
> (
> TransplantEventID ASC,
> DonorID ASC,
> FactID ASC
> ....
>
>
> Thanks!
>
>
>