SQL Server row to column, column career

One or more rows into a column (and separated by ",")

Table Name: A

Table data:

Desired Query Results:

Query statement:

Select  Name,        value = (STUFF (select    ', ' + value from      A                          WHERE     name = Test.name                        for< C10/>xml PATH ("))                        , 1, 1,")) from A as    testgroup by name;

The Ps:stuff statement is to get rid of the first "comma"

Attached stuff usage: (replace three characters with the following characters from the second start of the original character)

Query Result: AIJKLMNEF

two or one columns turn into multiple rows

Table name: TB

Table data:

Desired Result:

Query statement:

Select A.[name],b.[value]from (SELECT [Name],[value]=cast (' <v> ' +replace ([value], ', ', ' </v><v> ') + ' </v> ' as XML) from TB Aouter APPLY (SELECT [Value]=t.c.value ('. ', ' varchar () ') from A.[value].nodes ('/V ') as T (C) ) b

three, row to column (turn to arrogant god Zhang Zhitao's blog http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html)

1. Create a form

IF object_id (' TB ') is not NULL DROP table tbgocreate table TB (name varchar (10), Course VARCHAR (10), fractional INT) INSERT into TB VALUES (' Zhang San ', ' language ', ', ') insert into TB values (' Zhang San ', ' math ', ' $ ') insert into TB values (' Zhang San ', ' physical ', ') insert into TB values (' John Doe ', ' language ', 74) INSERT into TB values (' John Doe ', ' math ', ' + ') insert into TB values (' John Doe ', ' physical ', 94) goselect * from TB

2. Using SQL Server 2000 static SQL

SELECT name, Max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then score else 0 end) Math, Max (case course when ' physics ') Then fractions ELSE 0 end) physical from Tbgroup by name

3. Using SQL Server 2005 static SQL

SELECT  *from    TB PIVOT (MAX (score) for course in (language, mathematics, physics)) A;

4. Using SQL Server 2005 Dynamic SQL

--using stuff () DECLARE @sql VARCHAR (8000) SET @sql = '  --Initialize variable @sqlselect @[email protected]+ ', ' + course from TB GROUP by course-variable Multivalued assignment SET @sql =stuff (@sql, 1, 1, ")--Remove the first ', ' SET @sql = ' SELECT * from TB pivot (MAX (score) for course in (' [email protected]+ ')) a ' 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 ' EXEC (@sql)

The results of the row and column are combined with the total score, the average

1. Using SQL Server 2000 static SQL

SELECT name, Max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then fraction else 0 end) Math, Max (case course when ' physical ' TH EN fraction ELSE 0 end) physics, sum (Score) Total, cast (avg (fractional *1.0) as DECIMAL (18,2)) average from Tbgroup by name

2. Using SQL Server 2000 dynamic SQL

DECLARE @sql VARCHAR SET @sql = ' select name ' select @[email protected]+ ', max (case course when ' + course + ' then fraction else 0 end) ['  + course + '] ' from (SELECT DISTINCT course from TB) ASET @[email protected]+ ', sum (score) Total, cast (avg (fractional *1.0) as decimal (18,2)) average from TB Group by name ' EXEC (@sql)

3. Using SQL Server 2005 static SQL

Select M.*,n. Total score, N. Average from (SELECT * from TB pivot (MAX (score) for course in (language, mathematics, physics)) m, (SELECT name, sum (score) Total, cast (avg (fractional *1.0) As DECIMAL (18,2)) average of Tbgroup by name) nwhere M. name =n. Name

4. Using SQL Server 2005 Dynamic SQL

--using stuff ()--declare @sql VARCHAR (8000) SET @sql = "  --Initialize variable @sqlselect @[email protected]+ ', ' + course from TB GROUP by course-- Variable multi-value assignment-Same as Select @sql = @sql + ', ' + course from (SELECT DISTINCT course from TB) ASET @sql =stuff (@sql, 1, 1, ")--remove first ', ' SET @sql = ' Select m.*, N. Total score, N. Average from (SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' [email protected]+ ')) b) m, (select name , sum (Score) Total, cast (avg (fractional *1.0) as decimal (18,2)), average from TB group by name nwhere m. name = N. Name ' exec ' (@sql)-or use IsNull () DECLARE @sql VARCHAR (8000) SELECT @sql =isnull (@sql + ', ', ') + course from TB GROUP by course set @sql = ' SELECT m.*, N. Total score, N. Average from (SELECT * f Rom (select * from TB) a pivot (max (score) for course in (' [email protected]+ ')) b) m, (select name, sum (score) Total, cast (avg (fractional *1.0) as Decimal (18,2)) average from TB group by name nwhere m. name = N. Name ' exec ' (@sql)

V. Change of career

1. Create a form

IF object_id (' TB ') is not NULL DROP table tbgocreate table TB (name VARCHAR (10), language int, math int, physical int) INSERT into TB VALUES (' Zhang San ', 74,83,93) INSERT into terabytes VALUES (' John Doe ', 74,84,94) goselect * from Tbgo

2. Using SQL Server 2000 static SQL

--sql SERVER 2000 Static SQL.  SELECT * FROM (select name, course = ' language ', score = language from TB union ALL select Name, course = ' math ', score = Math from TB UNION ALL select Name, course = ' physical ', fraction = physical From TB) Torder by name, case course when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 end

2. Using 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 ' from Syscolumnswhere name!= ' name ' and id=object_id (' TB ')--table name TB, does not contain columns named as names for other columns order by Colidexec ( @sql + ' ORDER by name ') go

3. Using SQL Server 2005 static SQL

--sql SERVER 2005 Dynamic Sqlselect name, course, score from TB Unpivot (score for course in ([Language],[Math],[physics]) t

4. Using SQL Server 2005 Dynamic SQL

--sql SERVER 2005 Dynamic Sqldeclare @sql NVARCHAR (4000) SELECT @sql =isnull (@sql + ', ', ') +quotename (Name) from Syscolumnswhere id=object_id (' TB ') and name not in (' names ') ORDER by Colidset @sql = ' select name, [Course],[score] from TB Unpivot ([score] for [course] in (' [EMA Il protected]+ ')) B ' EXEC (@sql)

Thanks again! Zhang Zhitao

Transferred from: http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

Reprint: https://www.cnblogs.com/no27/p/6398130.html

SQL Server row to column, column career