SQL Server row to column, column change. Turn multiple rows into a column

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