Home Forums SQL Server 2008 T-SQL (SS2K8) what's the best way to get rows to columns for this? RE: what's the best way to get rows to columns for this?

  • This will do most of what you ask.  You don't have a field that supports a sort consistent with your expected output, so I've used the one that comes closest.

    ;
    WITH CTE AS
    (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY lid ORDER BY dat) AS rn
        FROM #temptest
    )

    SELECT lid,
        MAX(CASE WHEN rn = 1 THEN dat END) AS dat1,
        MAX(CASE WHEN rn = 2 THEN dat END) AS dat2,
        MAX(CASE WHEN rn = 3 THEN dat END) AS dat3
    FROM CTE
    GROUP BY lid

    Drew

    PS: I could add a CASE expression to force your data into the right order, but that is unlikely to transfer well to your real data.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA