How to Use a PIVOT

[aliahad]

I need to know how can I use PIVOT , my query

SELECT
A.HIS_STD_OID,
B.HIM_SERIES_ID
FROM
il_cps_conversion.dbo.HEALTH_IMMUNIZATION_SERIES A
INNER JOIN il_cps_conversion.dbo.HEALTH_IMMUNIZATION_DEFINITION B
ON A.HIS_HIM_OID = B.HIM_OID
WHERE
1=1
--AND STDNT.STD_OID = A.HIS_STD_OID
AND A.HIS_FIELDB_001 = '0'
AND A.HIS_STD_OID = 'std01000001256'
AND B.HIM_SERIES_ID <> 'TD'

returns following data set with six rows

and I need one row with all the six series_id . Like below:

std01000001256: DTP, MMR, MUM, RUB, TDP, VAR

Any suggestion will help.

 

[fredericofonseca]

what you need is "for xml path"

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        -- You only want to combine rows for a single ID here:
        WHERE T2.ID = T1.ID
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id

adapt to your own code as it should be easy and you will possibly learn what it does.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886