Pivoting string fields in SQL Server 2008 R2

Question

I need to create a dataset based on one which is generated from a series of SQL sub-queries. The results of the sub-queries can be replicated using the following code:

CREATE TABLE Table1
    (`StayID` varchar(3), `IncidentOrder` int, `TxDetails` varchar(73))
;

INSERT INTO Table1
    (`StayID`, `IncidentOrder`, `TxDetails`)
VALUES
    ('IP1', 1, 'Ward: A9999 - 01/01/2015 - 15:23'),
    ('IP1', 2, 'Consultant: Joe Bloggs Specialty :GERIATRIC MEDICINE - 02/01/2015 - 08:17'),
    ('IP1', 3, 'Discharge - 06/02/2015 - 16:40'),
    ('IP2', 1, 'Consultant: Joe Bloggs - 01/01/2015 - 09:02'),
    ('IP2', 2, 'Consultant: Joe Bloggs Specialty :GERIATRIC MEDICINE - 02/01/2015 - 12:56'),
    ('IP2', 3, 'Ward: A9999 - 02/01/2015 - 19:39'),
    ('IP2', 4, 'Consultant: Joe Bloggs - 05/01/2015 - 08:22'),
    ('IP3', 1, 'Ward: A9999 - 02/01/2015 - 04:58'),
    ('IP3', 2, 'Consultant: Joe Bloggs Specialty :GASTROENTEROLOGY - 02/01/2015 - 07:27'),
    ('IP3', 3, 'Consultant: Joe Bloggs - 05/01/2015 - 09:06'),
    ('IP3', 4, 'Ward: A9999 Consultant: Joe Bloggs - 05/01/2015 - 16:45'),
    ('IP3', 5, 'Ward: A9999 Consultant: Joe Bloggs - 05/01/2015 - 17:10'),
    ('IP3', 6, 'Ward: A9999 - 05/01/2015 - 18:14')
;

I need to produce results as follows:

Columns: StayID, Tx1, Tx2, Tx3, Tx., Txn

with TransferDetails being populated for each matching StayID and Txn column (based on IncidentOrder).

The IncidentOrder is not fixed and could be 10, 30, or 1 for each StayID in the data set, so a static pivot is not an option.

I have tried (and failed) producing the required output using PIVOT and am hopeful someone here can help.

Thanks in advance

please add expected output

Dmitrij Kultasev
– Dmitrij Kultasev

2015-04-29 17:23:52 +00:00
Commented Apr 29, 2015 at 17:23 — Dmitrij Kultasev
– Dmitrij Kultasev, Commented Apr 29, 2015 at 17:23

nik · Accepted Answer · 2015-04-29 20:48:28Z

1

Check this dynamic query,

DECLARE @ColName VARCHAR(MAX),
        @qry VARCHAR(MAX)

SELECT @ColName = ISNULL(@ColName + ',','') + QUOTENAME(IncidentOrder)
FROM (SELECT DISTINCT IncidentOrder FROM Table1) AS cols

SET @qry = 'SELECT StayID, ' + @ColName + ' FROM
            (
                SELECT StayID, IncidentOrder, TxDetails
                FROM   Table1
            ) AS Tbl
            PIVOT
            (
                MAX(TxDetails) FOR IncidentOrder
                IN (' + @ColName + ')
            )
            AS pvt'

EXEC(@qry)

See the fiddle here Fiddle

answered Apr 29, 2015 at 20:48

nik

1344 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Renzosilv · Accepted Answer · 2015-04-29 17:34:51Z

0

Do you have an infinite number of incidents ? Do you want your pivoted data to only have columns for incidents which actually exist ?

If you answered no to both questions I think the following query might help you.

SELECT 
STAYID,
DECODE(INCIDENTORDER,'1',TxDetails,NULL) AS Tx1,
DECODE(INCIDENTORDER,'2',TxDetails,NULL) AS Tx2,
DECODE(INCIDENTORDER,'3',TxDetails,NULL) AS Tx3,
.
.
DECODE(INCIDENTORDER,'n',TxDetails,NULL) AS Txn
FROM Table1
GROUP BY
STAYID;

answered Apr 29, 2015 at 17:34

Renzosilv

12 bronze badges

Collectives™ on Stack Overflow

Pivoting string fields in SQL Server 2008 R2

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related