please help to convert column to row in sql

Question

Post reply

please help to convert column to row in sql

panneermca35

Mr or Mrs. 500

Points: 504
More actions
March 7, 2016 at 11:05 pm

#399818

Hi I have table like
customerid 101
customernameRam
gender Male
married Y
customerid 102
customernameSham
gender Male
married Y
customerid 103
customernameTom
gender Male
married N
But I want the result set like below
customerid customername gender Married
101 ram Male Y
102 sham Male Y
103 tom Male N
Please help to do this. If the script for dynamic columns is more appreciate.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Could you please provide DDL (create table), sample data (as insert script) and expected output from that sample?

😎

Jon-183221 SSCommitted Points: 1908 More actions · Answer 2

Can you provide more information please, I dont fully understand what the problem is?

drew.allen SSC Guru Points: 76985 More actions · Answer 3

You have to remember that tables are sets and therefore inherently UNORDERED. You cannot depend on the presentation order to produce your desired results and there is no field that we can use to either link or order your records to reliably produce your desired results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Y.B. SSChampion Points: 11649 More actions · Answer 4

You got yourself into trouble because your table isn't normalized. This is just a bad way of storing data. Now assuming you had a unique ID per person in that table you could use a cross tab to do what you want. Note how I created the sample data. In the future please add that with your question and you will see a huge difference in how people will be willing to help. Welcome to the forums, cheers!

DECLARE @myTable TABLE (ID INT, SomeKey VARCHAR(50), SomeValue VARCHAR(20))

INSERT INTO @myTable

VALUES (1, 'customerid','101'),(1, 'customername','Ram'),(1, 'gender','Male'),(1, 'married','Y'),(2, 'customerid','102'),(2, 'customername','Sham'),(2, 'gender','Male'),(2, 'married','Y'),(3, 'customerid','103'),(3, 'customername','Tom'),(3, 'gender','Male'),(3, 'married','N')

SELECT * FROM @myTable

SELECT

ID,

MAX(CASE WHEN SomeKey = 'customerid' THEN SomeValue END) AS CustomerID,

MAX(CASE WHEN SomeKey = 'customername' THEN SomeValue END) AS CustomerName,

MAX(CASE WHEN SomeKey = 'gender' THEN SomeValue END) AS Gender,

MAX(CASE WHEN SomeKey = 'married' THEN SomeValue END) AS Married

FROM

@myTable

GROUP BY

ID

SELECT quote FROM brain WHERE original = 1
0 rows returned

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

does the relate to your previous question

http://www.sqlservercentral.com/Forums/FindPost1767204.aspx

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day