Displaying Columns as Rows in SQL Server 2005

Displaying Columns as Rows in SQL Server 2005

In order to get the result that you want you need to first UNPIVOT the data and then PIVOT theDatePeriod` Values.

The UNPIVOT will transform the multiple columns of Transactions, Customers and Visits into multiple rows. The other answers are using a UNION ALL to unpivot but SQL Server 2005 was the first year the UNPIVOT function was supported.

The query to unpivot the data is:

select dateperiod,
  col, value
from transactions
unpivot
(
  value for col in (Transactions, Customers, Visits)
) u

See Demo. This transforms your current columns into multiple rows, so the data looks like the following:

| DATEPERIOD |          COL | VALUE |
-------------------------------------
|   Jan 2012 | Transactions |   100 |
|   Jan 2012 |    Customers |    50 |
|   Jan 2012 |       Visits |   150 |
|   Feb 2012 | Transactions |   200 |

Now, since the data is in rows, you can apply the PIVOT function to the DatePeriod column:

select col, [Jan 2012], [Feb 2012], [Mar 2012]
from
(
  select dateperiod,
    t.col, value, c.SortOrder
  from
  (
    select dateperiod,
      col, value
    from transactions
    unpivot
    (
      value for col in (Transactions, Customers, Visits)
    ) u
  ) t
  inner join
  (
    select 'Transactions' col, 1 SortOrder
    union all
    select 'Customers' col, 2 SortOrder
    union all
    select 'Visits' col, 3 SortOrder
   ) c
    on t.col = c.col
) d
pivot
(
  sum(value)
  for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])
) piv
order by SortOrder;

See SQL Fiddle with Demo.

If you have an unknown number of date period's then you will use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(dateperiod) 
                    from transactions
                    group by dateperiod, PeriodNumberOverall
                    order by PeriodNumberOverall
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col, ' + @cols + ' 
             from 
             (
                select dateperiod,
                  t.col, value, c.SortOrder
                from
                (
                  select dateperiod,
                    col, value
                  from transactions
                  unpivot
                  (
                    value for col in (Transactions, Customers, Visits)
                  ) u
                ) t
                inner join
                (
                  select ''Transactions'' col, 1 SortOrder
                  union all
                  select ''Customers'' col, 2 SortOrder
                  union all
                  select ''Visits'' col, 3 SortOrder
                 ) c
                  on t.col = c.col
            ) x
            pivot 
            (
                sum(value)
                for dateperiod in (' + @cols + ')
            ) p 
            order by SortOrder'

execute(@query)

See SQL Fiddle with Demo. Both will give the result:

|          COL | JAN 2012 | FEB 2012 | MAR 2012 |
-------------------------------------------------
| Transactions |      100 |      200 |      300 |
|    Customers |       50 |      100 |      200 |
|       Visits |      150 |      300 |      600 |

SQL Server 2005 - Columns to Row

You do not need the PIVOT function for this. A pivot will convert the plant rows into separate columns.

To get the result that you want you can use FOR XML PATH:

select distinct t1.[key],
  STUFF((SELECT distinct ', ' + t2.plant
         from yourtable t2
         where t1.[key] = t2.[key]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') plant
from yourtable t1;

See SQL Fiddle with Demo

How do i transform rows into columns in sql server 2005

There are similar questions here,here answered in stackoverflow.

You need to use the operator PIVOT in your query to acheive this.Here is the example and explanation on how you can do that.The example is referenced from this source.

---I assumed your tablename as TESTTABLE---
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t.Name
                        FROM    TESTTABLE AS t
                        ORDER BY '],[' + t.Name
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

SET @query = N'SELECT '+ @cols +' FROM
(SELECT t1.Name , t1.Count FROM TESTTABLE AS t1) p
PIVOT (MAX([Count]) FOR Name IN ( '+ @cols +' ))
AS pvt;'

EXECUTE(@query)

Explanation

1.The first part of the query

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                        '],[' + t.Name
                FROM TESTTABLE AS t
                ORDER BY '],[' + t.Name
                FOR XML PATH('')
              ), 1, 2, '') + ']'

gives you a nice flattened result of your Name column values in a single row as follow

[Cheryl],[Drew],[Karen],[Kath],[Kirk],[Matt]  

You can learn more about the STUFF and XML PATH here and here.

2.SELECT + @cols + FROM will select all the rows as coloumn names for the final result set (pvt - step 3)

i.e

Select [Chery],[Drew],[Morgan],[Kath],[Kirk],[Matt] 

3.This query pulls all the rows of data that we need to create the cross-tab results. The (p) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.

(SELECT t1.Name, t1.Count FROM  TESTTABLE AS t1) p

4.The PIVOT expression

PIVOT (MAX (Count) FOR Name IN ( @cols) AS pvt

does the actual summarization and puts the results into a temporary table called pvt as

Chery | Drew | Morgon | Kath | Kirk | Matt  
-------------------------------------------  
257     1500     13      500    200    76

converting rows into columns in t-sql - sql server 2005

Assuming your data is in aTable:

create  FUNCTION toCSV (@id int)

RETURNS varchar(100)

AS

BEGIN

DECLARE @List varchar(100)

SELECT @List = COALESCE(@List + ', ', '') + 
   CAST(name AS varchar(10))

FROM aTable

WHERE ID = @id

RETURN(@list)

END;

go

Then:

select distinct id, dbo.toCSV(id) from aTable

SQL Server 2005 Rows to Columns

You might check the information in SQL Server dynamic PIVOT query? since you don't know how many columns you will have.

Also, you can potentially use PIVOT as it was introduced in SQL Server 2005:

When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher. For information about how to set the database compatibility level, see sp_dbcmptlevel (Transact-SQL) - MSDN

Row into column SQL Server 2005/8

You have to use UNPIVOT table operator for this, like this:

SELECT col, val
FROM Tablename AS t
UNPIVOT
(
   Val
   FOR Col IN (T1, T2, ..., T20)
) AS u;

SQL Fiddle Demo.

---

Update 1

If you want to do this dynamically for any number of columns, without the need to write them manually, the only way I can think of is by reading these columns from the table information_schema.columns to get the list of columns' names of the table. Then use dynamic SQL to compose the statement FOR col IN ... dynamically like this:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(column_name)
                      FROM information_schema.columns
                      WHERE table_name = 'tablename'
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = ' SELECT col, val
                  FROM tablename AS t
                  UNPIVOT
                  (
                  val
                  FOR col IN ( ' + @cols + ' )
                  ) AS u;';

EXECUTE(@query);

Updated SQL Fiddle Demo

This will give you:

| COL | VAL |
-------------
|  T1 |   1 |
| T10 |  15 |
| T11 |  33 |
| T12 |  31 |
| T13 |  12 |
| T14 |  10 |
| T15 |  12 |
| T16 |   9 |
| T17 |  10 |
| T18 |   2 |
| T19 |  40 |
|  T2 |   0 |
| T20 |  21 |
|  T3 |   9 |
|  T4 |   2 |
|  T5 |   3 |
|  T6 |  10 |
|  T7 |  14 |
|  T8 |  15 |
|  T9 |  20 |

Display horizontal rows vertically sql server 2005

Maybe something like this:

First some test data:

CREATE TABLE tblTempValues
    (
        EMPID VARCHAR(100), 
        ENAME VARCHAR(100),
        SALARY INT, 
        DEPARTMENT VARCHAR(100)
    )

INSERT INTO tblTempValues
VALUES
    ('01','TEST1',2000,'A/C'),
    ('02','TEST2',3000,'SALES')

The get the columns to PIVOT on. I use the ROW_NUMBER over the EMPID:

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY tblTempValues.EMPID) AS RowNbr
    FROM
        tblTempValues
)
SELECT
    @cols=COALESCE(@cols +','+QUOTENAME(RowNbr),QUOTENAME(RowNbr))
FROM
    CTE

Then do a dynamic pivot like this. The orderWeight so that you will have the order of the columns:

DECLARE @query NVARCHAR(4000)=
N';WITH CTE
AS
(
    SELECT ''EMPID'' AS ID, CAST(EMPID AS VARCHAR(MAX)) AS Value, ROW_NUMBER() OVER(ORDER BY EMPID) AS RowId,1 as orderWeight FROM tblTempValues UNION ALL
    SELECT ''ENAME'' AS ID, CAST(ENAME AS VARCHAR(MAX)) AS Value, ROW_NUMBER() OVER(ORDER BY EMPID) AS RowId,2 as orderWeight FROM tblTempValues UNION ALL
    SELECT ''SALARY'' AS ID, CAST(SALARY AS VARCHAR(MAX)) AS Value, ROW_NUMBER() OVER(ORDER BY EMPID) AS RowId,3 as orderWeight FROM tblTempValues UNION ALL
    SELECT ''DEPARTMENT'' AS ID, CAST(DEPARTMENT AS VARCHAR(MAX)) AS Value, ROW_NUMBER() OVER(ORDER BY EMPID) AS RowId,4 as orderWeight FROM tblTempValues
)
SELECT
    ID,'+@cols+'
FROM
    CTE
PIVOT
(
    MAX(Value)
    FOR RowId IN('+@cols+')

) AS p'

EXECUTE(@query)

Then in my case I will drop the temp table

DROP TABLE tblTempValues

How to convert rows values into column in sql server 2005

SELECT UserID, VendorName, [abc1], [abc2], [abc3]
FROM Questions
PIVOT (MAX(AnswerText)
    FOR QuestionText IN ([abc1], [abc2], [abc3])
) AS pvt

---

Related Topics