CODEWITHSUNDEEP
sqlsql-serverunpivot
JassyJov
JassyJov

Reputation: 204

Pivot dataset with column names

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'[email protected]' 
        )

Is it possible to get following result set:

    CustomerName   Customer1
    BirtDate       1990-12-05 
    BirtPlace      Place1
    Phone          +000125456789
    Email          [email protected]

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

Upvotes: 2

Views: 79

Answers (4)

taotechnocom
taotechnocom

Reputation: 228

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

Upvotes: 2

Blank
Blank

Reputation: 12378

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;

Upvotes: 0

jpw
jpw

Reputation: 44891

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

Upvotes: 3

Dark Shadow
Dark Shadow

Reputation: 35

 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', '[email protected]' 
    );

I hope i could help you

Have a nice Day

Upvotes: -1

Related Questions