One or more rows into a column (and separated by ",")
Table Name: A
Table data:
Desired Query Results:
Query statement:
SELECT the name,
Value = (STUFF((SELECT ', '+ value)
FROM A
WHERE name = Test. The name
The FOR
XML PATH (' ')
), 1, 1, ()
FROM the AS A Test
GROUP BY name;
PS: STUFF is just to get rid of the first comma.
STUFF usage :(from the second of the original character, three characters are replaced with the following characters)
SELECT STUFF(' abcdef ', 2, 3, 'ijklmn');
Query result: aijklmnef
Two, one column into multiple rows
The table name: TB
Table data:
Desired results:
Query statement:
SELECT a. [name], [value]. B.
The FROM (SELECT [name], [value] = CAST (' < v > '+ REPLACE ([value],' and ', '< / n > < n >') + "< / n >" AS XML) FROM TB) a
OUTER APPLY (SELECT [value]= t.c.value ('. ', 'varchar(50)') FROM a.[value].nodes(' /v ') AS T(C)) b
Three turn, rows, columns, since the great god zhi-tao zhang's blog, http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html)
1. Establish the table
IF OBJECT_ID(' TB ') IS NOT NULL DROP TABLE TB
The go
CREATE TABLE TB (name VARCHAR(10), course VARCHAR(10), score INT)
INSERT INTO TB VALUES(' zhang SAN ', 'Chinese',74)
INSERT INTO TB VALUES(' zhang SAN ', 'math',83)
INSERT INTO TB VALUES(' zhang SAN ', 'physics',93)
INSERT INTO TB VALUES(' li si ', 'Chinese',74)
INSERT INTO TB VALUES(' li si ', 'math',84)
INSERT INTO TB VALUES(' li si ', 'physics',94)
The go
SELECT * FROM TB
2. Use SQL Server 2000 static SQL
SELECT name,
Max (CASE course WHEN 'Chinese' THEN score ELSE 0 end) Chinese,
Max (CASE course WHEN 'math' THEN fraction ELSE 0 end) math,
Max (CASE course WHEN 'physics' THEN fraction ELSE 0 end) physics
The FROM TB
GROUP BY name
3. Use SQL Server 2005 static SQL
SELECT *
FROM TB PIVOT(MAX) FOR courses IN (Chinese, mathematics, physics) a;
4, use SQL Server 2005 dynamic SQL
- use the stuff ()
DECLARE @ SQL VARCHAR (8000).
set@sql = ' '-- initializes the variable @sql
SELECT @[email protected]+ ', '+ course FROM TB GROUP BY course -- variable multiple value assignment
set@sql =stuff(@sql,1,1, ' ')-- remove the first ', '
set@sql = 'select * from TB pivot (Max score) for course in (' [email protected]+'))a '
The exec (@ SQL)
-- or use isnull()
DECLARE @ SQL VARCHAR (8000).
SELECT @sql=isnull(@sql+ ', ', ' ')+ course FROM TB GROUP BY course
set@sql = 'select * from TB pivot (Max score) for course in (' [email protected]+'))a '
The exec (@ SQL)
4. The result of row rotation plus the total score and average score
1. Use SQL Server 2000 static SQL
SELECT name,
Max (CASE course WHEN 'Chinese' THEN score ELSE 0 end) Chinese,
Max (CASE course WHEN 'math' THEN fraction ELSE 0 end) math,
Max (CASE course WHEN 'physics' THEN fraction ELSE 0 end) physics,
Sum total,
Cast (avg(score *1.0)AS DECIMAL(18,2)) average score
The FROM TB
GROUP BY name
2, use SQL Server 2000 dynamic SQL
DECLARE @ SQL VARCHAR (500).
set@sql = 'select name'
SELECT @[email protected]+ ', Max (case course when ' '+ course +' ' ' 'then score else 0 end)[' + course +' '] '
From (SELECT DISTINCT courses from TB)a
SET @[email protected]+ ',sum(score) total,cast(avg(score *1.0) as decimal(18,2) average score from TB group by name '
The exec (@ SQL)
3. Use SQL Server 2005 static SQL
SELECT m.*, SELECT m.*, SELECT m
The from
SELECT * FROM TB pivot(Max)FOR course IN(Chinese, mathematics, physics)a)m,
(SELECT name,sum(score) total score,cast(avg(score *1.0)AS DECIMAL(18,2)) average score
The FROM TB
GROUP BY name)n
WHERE are you from
4, use SQL Server 2005 dynamic SQL
- use the stuff ()
--
DECLARE @ SQL VARCHAR (8000).
set@sql = ' '-- initializes the variable @sql
SELECT @[email protected]+ ', '+ course FROM TB GROUP BY course -- variable multiple value assignment
Select @sql = @sql + ', '+ courses from (select distinct courses from TB)a
set@sql =stuff(@sql,1,1, ' ')-- remove the first ', '
set@sql = 'select m.*,n
Select * from (select * from TB) a pivot (Max (score) for course in (' [email protected]+ ')) b) m,
(select name,sum(score) total, cast(avg(score *1.0) as decimal(18,2)) average score from TB group by name) n
Where are you from? Where are you from?
The exec (@ SQL)
-- or use isnull()
DECLARE @ SQL VARCHAR (8000).
SELECT @sql=isnull(@sql+ ', ', ' ')+ course FROM TB GROUP BY course
set@sql = 'select m.*, n
Select * from (select * from TB) a pivot (Max (score) for course in (' [email protected]+ ')) b) m,
(select name,sum(score) total, cast(avg(score *1.0) as decimal(18,2)) average score from TB group by name) n
Where are you from? Where are you from?
The exec (@ SQL)
5. Column switching
1. Establish the table
IF OBJECT_ID(' TB ')IS NOT NULL DROP TABLE TB
The go
CREATE TABLE TB (name VARCHAR(10), Chinese INT, math INT, physics INT)
INSERT INTO TB VALUES(' zhang SAN ',74,83,93)
INSERT INTO TB VALUES(' li si ',74,84,94)
The go
SELECT * FROM TB
The go
2. Use SQL Server 2000 static SQL
SQL SERVER 2000 static SQL.
SELECT * FROM
(
SELECT name, course = 'Chinese', score = Chinese FROM TB
UNION ALL
SELECT name, course = 'math', score = math FROM TB
UNION ALL
SELECT name, course = 'physics', score = physics FROM TB
) t
ORDER BY name,CASE WHEN 'Chinese' THEN 1 WHEN 'mathematics' THEN 2 WHEN' physics' THEN 3 end
2, use SQL Server 2000 dynamic SQL
--SQL SERVER 2000 dynamic SQL.
-- call system table dynamic ecology.
DECLARE @ SQL VARCHAR (8000).
SELECT @sql=isnull(@sql+ 'union all', ' ')+ 'SELECT name, [course]='
+ quotename (Name, ' ' ' ') + ', [score] = '+ quotename (Name) +' from TB '
The FROM syscolumns
WHERE the Name! = 'name' AND ID=object_id(' TB ')-- table name TB, without other columns with column names
The ORDER BY colid
Exec (@sql+ 'order by name')
The go
3. Use SQL Server 2005 static SQL
--SQL SERVER 2005 dynamic SQL
SELECT name, course, fraction FROM TB unpivot (fraction FOR course IN([Chinese],[mathematics],[physics])) t
4, use SQL Server 2005 dynamic SQL
--SQL SERVER 2005 dynamic SQL
DECLARE @ SQL NVARCHAR (4000).
SELECT @ SQL = isnull (@ SQL + ', ', ' ') + quotename (Name)
The FROM syscolumns
WHERE ID=object_id(' TB ')AND Name NOT IN(' Name ')
The ORDER BY Colid
set@sql = 'select name,[course],[score] from TB unpivot ([score] for [course] in(' [email protected]+'))b '
The exec (@ SQL)
From: http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html
SQL Server row to column, column to row. Multiple rows into one column