SQL CrossTab query - non-numeric data

Question

I have searched for SQL crosstab information and examples but nearly all of them are doing CROSSTABS/PIVOTS based on numeric data and my data is not numeric. I am using MS SQL Server

I have the following data in a single table:

DEVICE  DATATYPE        DATA
------  --------        ----
ABC     RELEASE-DATE    10/01/2013
ABC     VERSION         01A
ABC     BUILD-DATE      11/03/2014
ABC     TYPE            PROD
DEF     RELEASE-DATE    06/19/2014
DEF     VERSION         02G
DEF     BUILD-DATE      08/07/2014
GHI     RELEASE-DATE    12/11/2013
GHI     VERSION         01F
GHI     BUILD-DATE      01/03/2014
GHI     TYPE            DEV
JKL     RELEASE-DATE    10/01/2013
JKL     VERSION         01A
JKL     BUILD-DATE      06/25/2014
JKL     TYPE            PROD

My desired output would be as something like the following:

DEVICE  RELEASE-DATE    VERSION     BUILD-DATE  TYPE
------  ------------    -------     ----------  ----
ABC     10/01/2013      01A         11/03/2014  PROD
DEF     06/19/2014      02G         08/07/2014  
GHI     12/11/2013      01F         01/03/2014  DEV
JKL     10/01/2013      01A         06/25/2014  PROD

I have been doing this work manually in Excel using VLOOKUPS, but would like to have a solution using SQL if that is possible.

Can anyone help me on this? Thanks in advance!

score 1 · Accepted Answer · answered Dec 03 '14 at 19:25

1

Use MAX (or MIN) for the non-numeric data:

SELECT *
FROM   MyTable
PIVOT  (MAX(Data)
          FOR DataType IN ([RELEASE-DATE], [Version], [BUILD-DATE], [TYPE])) pvt

answered Dec 03 '14 at 19:25

Code Different

90,614
16
144
163

1

Thanks so much. I never thought of using MIN or MAX as the aggregate function! – mconwell Dec 03 '14 at 19:42

score 1 · Answer 2 · answered Dec 03 '14 at 19:26

1

use pivot, which converts rows to columns.

SELECT DEVICE, [RELEASE-DATE],[VERSION],[BUILD-DATE],[TYPE] FROM Table1
PIVOT
( max(DATA) for DATATYPE in ( [RELEASE-DATE],[VERSION],[BUILD-DATE],[TYPE])
)pvt

answered Dec 03 '14 at 19:26

radar

13,270
2
25
33

SQL CrossTab query - non-numeric data

2 Answers2