Transpose rows and columns without aggregate

Question

I have the following dataset

Date Field1 Col1 Col2 Col3 2012/07/02 Customer1 CL DS RT 2012/07/03 Customer1 DS RT 700 2012/07/04 Customer1 DS RT 700 2012/07/02 Customer2 CL DS RT 2012/07/03 Customer2 DS RT 1500 2012/07/04 Customer2 DS RT 1500 2012/07/02 Customer3 CL DS RT 2012/07/03 Customer3 DS RT 6000 2012/07/04 Customer3 DS RT 6000 2012/07/02 Customer4 CL RC RT 2012/07/03 Customer4 RC RT 4900 2012/07/04 Customer4 RC RT 4900 

I would like to output as follows:

 Field1 2012/07/02 2012/07/03 2012/07/04 Col1 Customer1 CL DS DS Col2 Customer1 DS RT RT Col3 Customer1 RT 700 700 Col1 Customer2 CL DS DS Col2 Customer2 DS RT RT Col3 Customer2 RT 1500 1500 Col1 Customer3 CL DS DS Col2 Customer3 DS RT RT Col3 Customer3 RT 6000 6000 Col1 Customer4 CL RC RC Col2 Customer4 RC RT RT Col3 Customer4 RT 4900 4900 

The problem is also that I have an uncommitted number of clients (field1) and an uncommitted number of dates.

Answer 1

What you are trying to do is called PIVOT . This can be done in two ways: either with a static bar, where you hard-code all the values ​​for the conversion, or dynamic points where the values ​​were determined at run time.

Static version (see SQL Fiddle with Demo ):

 select * from ( select dt, field1, col, value from yourTable unpivot ( value for col in (col1, col2, col3) ) u )x1 pivot ( min(value) for dt in ([2012-07-02], [2012-07-03], [2012-07-04]) ) p 

Dynamic Pivot (see SQL Fiddle with Demo ):

 DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) select @colsUnpivot = stuff((select ','+quotename(C.name) from sys.columns as C where C.object_id = object_id('yourTable') and C.name like 'col%' for xml path('')), 1, 1, '') select @colsPivot = STUFF((SELECT distinct ', ' + QUOTENAME(convert(char(10), dt, 120)) from yourTable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT col, field1, ' + +@colsPivot + ' FROM ( select dt, field1, col, value from yourTable unpivot ( value for col in ('+ @colsUnpivot+') ) u )x1 pivot ( min(value) for dt in (' +@colsPivot +') ) p ' exec(@query) 

A dynamic bar is a great option when you do not know the number of elements that need to be converted to columns. Both give the same results.

Change # 1 if you want the date columns to be in a specific order - desc, etc., you will use the following to get the dates (see SQL Fiddle with Demo ):

 select @colsPivot = STUFF((SELECT ', ' + QUOTENAME(convert(char(10), dt, 120)) from yourTable group by dt ORDER by dt desc FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') 

Answer 2

You do not need to do this without aggregates, but for this you need to use Dynamic SQL. Search for "SQL Dynamic Pivot" and you will find many posts about it. For example: SQL Server dynamic PIVOT query?

But it would be better to do this in the client, because SQL clients prefer to know which columns are produced ahead of time.

Answer 3

You should use SQL Server Pivot query for your question.

You can use below query:

 CREATE TABLE mytable(date DATE, Field1 NVARCHAR(50), col1 NVARCHAR(30), col2 NVARCHAR(30),col3 NVARCHAR(30)) INSERT INTO mytable(date,Field1,col1,col2,col3) VALUES('2012/07/02','Customer1','CL','DS','RT') ... SELECT Col, Field1, [2012/07/02],[2012/07/03],[2012/07/04] FROM (SELECT Field1, 'Col1' AS Col, Date, Col1 AS ColVal FROM MyTable UNION ALL SELECT Field1, 'Col2' , Date, Col2 AS ColVal FROM MyTable UNION ALL SELECT Field1, 'Col3' , Date, Col3 AS Col FROM MyTable )AS P PIVOT (MIN(Colval) FOR Date IN ([2012/07/02],[2012/07/03],[2012/07/04])) AS Pvt ORDER BY Field1, Col