SQL statements for Row-to-column Conversion

The format is as follows:

Customer sales by date
20061001 Carrefour 200
20061002 shanghaojia 300
20061003 gift 400
20061103 jiale new store 500
20061105 North le stores 600
20061106 Carrefour 200
20061006 North le stores 200
20061107 gift 400

The following format is generated:
Customer 200610 200611
Carrefour 200 200

 

-- Create dynamic SQL
Declare @ SQL varchar (8000)
Set @ SQL =''

Select @ SQL = @ SQL + ', [' + rtrim (Num) + '] = max (Case num when' + rtrim (Num) + 'then sales quantity end )'
From (select left (convert (varchar, datetime, 112), 6) as num from t a) B
Group by num order by num

Set @ SQL = 'select customer' + @ SQL + 'from (select. *, left (convert (varchar, datetime, 112), 6) as num from t a) B group by B. customer'

Exec (@ SQL)
Go

 

Complete test example

 

-- When the date field is string type
Create Table T (date varchar (10), customer varchar (10), sales quantity INT)
Insert T select '000000', 'carrefour ', 20061001
Union all select '000000', 'shanghaihaojia', 20061002
Union all select '000000', 'free', 20061003
Union all select '000000', 'jiale new store', 20061103
Union all select '000000', 'North happy store', 20061105
Union all select '000000', 'carrefour ', 20061106
Union all select '000000', 'North happy store', 20061006
Union all select '000000', 'free', 20061107

declare @ SQL varchar (8000)
set @ SQL = 'select customer, '
select @ SQL = @ SQL + quotename (date) + '= sum (case when left (date, 6) =' + quotename (date, ''') + 'then sales volume else 0 end ), '
from (
select date = left (date, 6) from T group by left (date, 6)
) TMP
select @ SQL = left (@ SQL, Len (@ SQL)-1 ), @ SQL = @ SQL + 'from T group by customer'
exec (@ SQL)

-- result
customer 200610 200611
---------- ----------- ---------
North le store 200 600
Carrefour 200 200
jiale store 0 500
> shanghaojia 300 0
GIFT 400 400