SQL Server row and column conversions

SQL Server row to column

In SQL Server 2005, Pivot is used to convert column values to column names (row to column), and in SQL Server 2000 there is no such keyword that can only be implemented with a case statement.

--Create test database use MASTERGOIF (exists (SELECT * from sys.databases where name = ' webdb '))    drop database webdbgocreate data Base webdb on primary (    name = ' webdb ',    filename = ' f:\database\webDB.mdf ',    size = 5MB,    maxsize = Unlimite D,    filegrowth = 10%) Log on (    name = ' Webdb_log ',    filename = ' f:\database\webDB_log.ldf ',    size = 3MB,    maxsize = 50mb,    filegrowth = 2mb) Use webdbgo--Create test table if (exists (SELECT * from sys.objects where name = ' student ') )    drop table studentgocreate table student (    ID int identity (primary) key,    name varchar () not null,< C14/>subject varchar () NOT NULL,    score int not null)    --insert test data inserts into student values (' Zhang San ', ' language ', 90), (' Zhang San ') , ' mathematics ', 100), (' Zhang San ', ' English ', 80 ', (' John Doe ', ' English ', 90), (' Harry ', ' language ', 90), (' John Doe ', ' language ', 90), (' John Doe ', ' math ', 70 '), (' Harry ', ' math ', 62), (' Harry ', ' English ', student) SELECT * FROM

SQL Server 2000 row to column

Select name as name,
sum (case [subject] when ' language ' then score else 0 end) as ' language ', sum (case [subject] when ' math ' then score else 0 end) as ' math ', S UM (case [subject] when ' English ' then score else 0 end) as ' English ' from student group by name

, it has been converted according to the column name specified in the script, but doing so requires knowing what data in the table is available as a column. This method is commonly referred to as a static method.

DECLARE @sql varchar Set @sql = ' Select name as name, ' Select @sql = @sql + ' sum (case [subject] when ' + [subject] + "Then score  else 0 end) as" + QUOTENAME ([subject]) + "', ' from (select DISTINCT [subject] from student) as S --Add a comma and then intercept the last comma select @sql = Left (@sql, Len (@sql)-1) + ' from student group by name ' Print (@sql) EXEC (@sql) Select Quotenam E (' AA[]BB ')--where quotename is used to make the string a valid identifier

This method does not need to know exactly what data needs to be converted as columns, it will automatically go to the data to find the data that is not duplicated, will be displayed as columns. This method is often called a dynamic method, and the SQL method is spliced.

SQL Server 2005 row to column

SELECT * FROM (    select Name,[subject],score to student) s pivot (SUM (score) for [subject] in (language, Math, English)) as Pvtorder by Pvt.name

Pivot syntax is: Pivot (aggregate function (column) for column in (value, value, value)) as P

This is static method row to column, how to code simple bar.

DECLARE @sql_str varchar (DECLARE) @sql_col varchar (+) Select @sql_col = ISNULL (@sql_col + ', ', ') + QUOTENAME ([subj ECT]) from student group by [subject]--first determine the column name to be converted set @sql_str = ' select * FROM (    select Name,[subject],score from Stud ENT) s pivot (SUM (score) for [subject] in (' + @sql_col + ')) as Pvtorder by Pvt.name ' Print (@sql_str) exec (@sql_str)

The dynamic creation method above 2005.

SQL Server Column Career

In SQL Server 2005, Unpivot is used to convert column names to values (column changers), which can only be implemented with union statements in SQL Server 2000.

Use webdbgo--Create test table if (exists (SELECT * from sys.objects where name = ' student '))    drop table Studentgocreate table Stu Dent (    ID int identity (primary) key,    name varchar () not NULL, language int not null,    English int not NULL,    number int NOT NULL)    --Inserts test data insert into student values (' Zhang San ', 87,90,62), (' John Doe ', 87,90,65), (' Harry ', 23,90,34) SELECT * FROM Student

Column changers in SQL Server 2000

SQL Server 2000 static methods

SELECT * FROM (    select name, course = ' language ', score = language from Student    UNION ALL    Select name, course = ' math ', score = math from student< C14/>union all    select name, course = ' English ', score = English from Student) t order by name, case course when ' language ' then 1 when ' math ' then 2 whe N ' English ' then 3 end

SQL Server 2000 Dynamic SQL

DECLARE @sql varchar (+) Select @sql = ISNULL (@sql + ' union All ', ') + ' select name, course = ' + QUOTENAME (name, ' "') + ', score = ' + QUOTENAME (name) + ' from student ' from syscolumns where id=object_id (' student ') and name is not in (' id ', ' name ') print (@ SQL) EXEC (@sql)

SQL Server 2005 Static SQL uses the UNPIVOT keyword

Select Name, course, score from student Unpivot (score for Course in (Chinese, English, maths)) s

SQL Server 2005 Dynamic SQL

DECLARE @sql varchar () Select @sql = IsNull (@sql + ', ', ') + QuoteName (name) from syscolumns where id = object_id (' Stud  Ent ') and name not in (' id ', ' name ') Order by Colidset @sql = ' Select Name, course, score from student Unpivot (score for Course in (' [email Protected]+ ')) s ' Print (@sql) exec (@sql)

SQL Server row and column conversions