Needed help to convert column as row values

Question

Post reply

Needed help to convert column as row values

veenavidyakaran

SSCommitted

Points: 1994
More actions
September 23, 2011 at 4:13 am

#390458

Can someone pls help out. I have a problem that i would need some assistance. i have a table as mentioned below
dealer_codedealer_nameregionmonth
A1001Ashadelhi4/1/2011
A1002Bobymumbai5/1/2011
A1003Vijaykolkata6/1/2011
A1004Rathijodhpur7/1/2011
A1005Gitagujrat8/1/2011
what i want accomplish is to have the above mentioned data in the below metioned format. Also the column dealer1 dealer2 dealer3..... changes dynamically
particularsdealer1dealer2dealer3dealer4dealer5
dealer_codeA1001A1002A1003A1004A1005
dealer_nameAshaBobyVijayRathiGita
regiondelhimumbaikolkatajodhpurgujrat
month4/1/20115/1/20116/1/20117/1/20118/1/2011
Pls provide me the solution for the above problem
It's very urgent

Viewing 7 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can UNPIVOT and then PIVOT again:

DECLARE @sampleData TABLE (

dealer_codechar(5) PRIMARY KEY,

dealer_namevarchar(10),

region varchar(10),

month datetime

)

INSERT INTO @sampleData VALUES ('A1001','Asha','delhi','4/1/2011')

INSERT INTO @sampleData VALUES ('A1002','Boby','mumbai','5/1/2011')

INSERT INTO @sampleData VALUES ('A1003','Vijay','kolkata','6/1/2011')

INSERT INTO @sampleData VALUES ('A1004','Rathi','jodhpur','7/1/2011')

INSERT INTO @sampleData VALUES ('A1005','Gita','gujrat','8/1/2011')

SELECT col AS particulars,

[1] AS dealer1,

[2] AS dealer2,

[3] AS dealer3,

[4] AS dealer4,

[5] AS dealer5

FROM (

SELECT

n = ROW_NUMBER() OVER(ORDER BY dealer_code),

dealer_code = CAST(dealer_code AS varchar(10)),

[dealer_name],

[region],

month = CONVERT(varchar(10),[month],112) FROM @sampleData )AS S

UNPIVOT (val FOR col IN ([dealer_code],[dealer_name],[region],[month])) AS U

PIVOT (MIN(val) FOR n IN ([1],[2],[3],[4],[5])) AS P

-- Gianluca Sartori

drew.allen SSC Guru Points: 77012 More actions · Answer 2

What you're asking to do violates basic principles of database design. Why do you need to do this?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

veenavidyakaran SSCommitted Points: 1994 More actions · Answer 3

veenavidyakaran

SSCommitted

Points: 1994

September 25, 2011 at 3:01 am

#1386179

It's a businees requirement.

veenavidyakaran SSCommitted Points: 1994 More actions · Answer 4

Hi,

Thanks a lot.

It's really helpful.

Regards,

Veena Vidyakaran

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 5

veenavidyakaran (9/25/2011)
It's a businees requirement.

Nah... not what we meant. What's the reason behind the business requirement? Is this going into a spreadsheet, another program that requires the format, or what? Knowing the answer to such things will sometimes allow us to come up with a better answer than you may have thought of.

It's very urgent

You're new here so let me offer a bit of advice on how to post "urgent" problems.

First, understand the we understand that every post is "Urgent". The word "Urgent" and its ruder equivalent "ASAP" have become two of the most hated words on this forum and are likely to draw long, drawn out rhethoric about how we're all volunteers doing this stuff out of the goodness of our hearts. Don't use the word "Urgent" or "ASAP" or anything remotely close to those two words in your post... ever.

The second thing is, there are some VERY conscientious people on this forum. A good number of us don't like to post code unless we've tested it with your data. Some of us will occasionally take the time to build the test data on our own and sometimes we won't. It depends on how many people we're helping in a given time frame. With that in mind, please read and heed the article at the first link in my signature line below. People who post their data in such a format will usually get "urgent" help without even asking because they're made it easy for people to help. Gianluca is one of the kinder members who will take the time to reformat your data to demonstrate code. Lots of us just don't have the time to do that for everyone.

Shifting gears back to the problem you posted...

[font="Arial Black"]Will you always have 5 dealers or could there be more or less?[/font] If there could be more or less, then Gianluca's fine code just won't do it for you. And now you also know more about why we ask why you need to do things and why short answers like "It's a business requirement" don't quite hack it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

veenavidyakaran (9/26/2011)
Hi,
privide me solution for dynamic change of column(Dealer1, Dealer2....) in the previous code i provided
Regards,
Veena Vidyakaran

You could use dynamic sql to build the UNPIVOT/PIVOT trick based on your actual data. Something like this:

IF OBJECT_ID('tempdb..#sampleData') IS NOT NULL

DROP TABLE #sampleData

CREATE TABLE #sampleData (

dealer_code char(5) PRIMARY KEY,

dealer_name varchar(10),

region varchar(10),

month datetime

)

INSERT INTO #sampleData VALUES ('A1001','Asha','delhi','4/1/2011')

INSERT INTO #sampleData VALUES ('A1002','Boby','mumbai','5/1/2011')

INSERT INTO #sampleData VALUES ('A1003','Vijay','kolkata','6/1/2011')

INSERT INTO #sampleData VALUES ('A1004','Rathi','jodhpur','7/1/2011')

INSERT INTO #sampleData VALUES ('A1005','Gita','gujrat','8/1/2011')

INSERT INTO #sampleData VALUES ('A1006','Gianluca','Conegliano','9/1/2011')

INSERT INTO #sampleData VALUES ('A1007','Jeff','Detroit','10/1/2011')

DECLARE @sql nvarchar(max)

DECLARE @dealer_count int

SELECT @sql = STUFF((

SELECT ',' + QUOTENAME(CAST(number AS varchar(10))) AS [text()]

FROM master.dbo.spt_values

WHERE type = 'P'

AND number BETWEEN 1 AND (SELECT COUNT(DISTINCT dealer_code) FROM #sampleData)

FOR XML PATH('')

), 1, 1, SPACE(0));

SELECT @sql = '

SELECT col AS particulars, ' + @sql + '

FROM (

SELECT

n = ROW_NUMBER() OVER(ORDER BY dealer_code),

dealer_code = CAST(dealer_code AS varchar(10)),

[dealer_name],

[region],

[month] = CONVERT(varchar(10),[month],112)

FROM #sampleData

)AS S

UNPIVOT (val FOR col IN ([dealer_code],[dealer_name],[region],[month])) AS U

PIVOT (MIN(val) FOR n IN ('+ @sql +')) AS P'

EXEC(@sql)

However... I tested it on a SQL 2005 SP4 instance and it doesn't work!!!!

Msg 8167, Level 16, State 1, Line 2

The type of column "month" conflicts with the type of other columns specified in the UNPIVOT list.

In SQL 2008R2 SP1 works just fine.

I don't know what's the reason behind. Looks like a bug to me. The same exact code, changing #sampleData (temp table) into @sampleDate (table variable) works fine on 2005.

Go figure.

Does this work in your environment?

A couple of minor points:

1) Listen to Jeff's suggestions. If it really is urgent, post your sample data: you'll get replies in a quicker fashion.

2) Don't PM further requirements: other people may benefit from your case

3) I know it could be a language issue and I'm totally aware that you appreciate the help you get from us, but... the word "please" is greatly appreciated.

-- Gianluca Sartori

Needed help to convert column as row values

Cookies on SQLServerCentral