Converting Columns into rows with their respective data in sql server

by

Sound like you want to UNPIVOT

Sample from books online:

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Returns: 

VendorID   Employee   Orders
---------- ---------- ------
1          Emp1       4
1          Emp2       3
1          Emp3       5
1          Emp4       4
1          Emp5       4
2          Emp1       4
2          Emp2       1
2          Emp3       5
2          Emp4       5
2          Emp5       5

see also: Unpivot SQL thingie and the unpivot tag

More Related Contents:

  1. SQL count rows in a table
  2. Count within a time period
  3. I need a SQL server Func that will calculate a complete date from the week of the year [closed]
  4. Cannot Connect to Server – A network-related or instance-specific error
  5. Turning a Comma Separated string into individual rows
  6. When should I use semicolons in SQL Server?
  7. Foreign Key to multiple tables
  8. How to get Time from DateTime format in SQL?
  9. Natural (human alpha-numeric) sort in Microsoft SQL 2005
  10. Get size of all tables in database
  11. Fuzzy matching using T-SQL
  12. How to get first and last day of previous month (with timestamp) in SQL Server
  13. CTE to get all children (descendants) of a parent
  14. SQL Server – inner join when updating [duplicate]
  15. How do I use the CONCAT function in SQL Server 2008 R2?
  16. How do I exclude Weekend days in a SQL Server query?
  17. EXEC sp_executesql with multiple parameters
  18. SQL Server – Dynamic PIVOT Table – SQL Injection
  19. Best data store for billions of rows
  20. newid() inside sql server function
  21. How can I use a single mssql connection pool across several routes in an Express 4 web application?
  22. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  23. Retrieving date in sql server, CURRENT_TIMESTAMP vs GetDate()
  24. SSIS reading LF as terminator when its set as CRLF
  25. What are some ways of accessing Microsoft SQL Server from Linux?
  26. LPAD in SQL Server 2008
  27. SQL Server error “Implicit conversion of because the collation of the value is unresolved due to a collation conflict.”
  28. How to choose the clustered index in SQL Server?
  29. How can I show the table structure in SQL Server query?
  30. INSERT INTO @TABLE EXEC @query with SQL Server 2000

Leave a Comment