Pivot dataset with column names

Question

if you have a table (example)

   declare @MyTable table (
CustomerName nvarchar(50),
BirthDate datetime,
BirtPlace nvarchar(50),
Phone nvarchar(50),
Email nvarchar(50)
)

insert into @MyTable
        (
         CustomerName,
         BirthDate,
         BirtPlace,
         Phone,
         Email
        )
values  (
         'Customer1', 
         '12.05.1990', 
         'Place1', 
         N'+000125456789', 
         N'customer@customer.com' 
        )

Is it possible to get following result set:

    CustomerName   Customer1
    BirtDate       1990-12-05 
    BirtPlace      Place1
    Phone          +000125456789
    Email          customer@customer.com

Something like pivot, but i don't have any idea how to get to this result.

you can refer this solution : **http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server** and **http://data.stackexchange.com/stackoverflow/query/497432** — NikuNj Rathod, Jul 01 '16 at 07:24

score 3 · Answer 1 · answered Jul 01 '16 at 07:33

3

As you want to change columns to rows the function you want is unpivot not pivot.

This should do the trick:

SELECT col, val
FROM 
( 
    SELECT 
        CustomerName, 
        CAST(BirthDate AS NVARCHAR(50)) BirthDate, 
        BirtPlace, 
        Phone, 
        Email  
    FROM @MyTable
) AS t
UNPIVOT 
(
    val FOR col IN (CustomerName, BirthDate, BirtPlace, Phone, Email)
) AS u

answered Jul 01 '16 at 07:33

jpw

44,361
6
66
86

Thanks this helps me – JassyJov Jul 01 '16 at 08:32

score 2 · Answer 2 · answered Jul 01 '16 at 08:16

Try this

SELECT myColumn, myDetail    
FROM
(
    SELECT
        CustomerName,
        CONVERT(NVARCHAR(50),BirthDate,121) AS BirthDate,
        BirtPlace,
        Phone,
        Email
    FROM
        @MyTable
) AS A
UNPIVOT
(
    myDetail FOR myColumn IN (CustomerName, BirthDate, BirtPlace, Phone, Email)
) AS tbUnpivot

score 0 · Answer 3 · answered Jul 01 '16 at 07:52

This is a unpivot issue, and if you used old version sql server 2000, this unpivot syntax will not work, then you can use UNION:

SELECT 'CustomerName' AS colName, CustomerName AS colVal FROM @MyTable
UNION
SELECT 'BirthDate' AS colName, CAST(BirthDate AS NVARCHAR(50)) AS colVal FROM @MyTable
UNION
SELECT 'BirthPlace' AS colName, BirthPlace AS colVal FROM @MyTable
UNION
SELECT 'Phone' AS colName, Phone AS colVal FROM @MyTable
UNION
SELECT 'Email' AS colName, Email AS colVal FROM @MyTable;

score -1 · Answer 4 · answered Jul 01 '16 at 07:24

-1

 SELECT CustomerName,BirtDate,BirtPlace,Phone,Email FROM @MyTable\G

You have no ID and no Foreign keys so i think thats a way to solve your problem. The \G give´s you the SQL-query as a list.

  values  (
     'Customer1', '1990-12-05', 'Place1', '+000125456789', 'customer@customer.com' 
    );

I hope i could help you

Have a nice Day

answered Jul 01 '16 at 07:24

Dark Shadow

35
8

1

The `\G` is a rather strange syntax and not appropriate with SQL Server. You might read this: http://stackoverflow.com/q/2277014/5089204 – Shnugo Jul 01 '16 at 07:56

Pivot dataset with column names

4 Answers4