rows into columns

Question

rows into columns

SQL_NuB

Hall of Fame

Points: 3666
More actions
March 31, 2011 at 7:00 am

#147771

After some more looking at my data I figured a pivot query may not work, so is there a way to make my rows into columns instead?
I get my data like this
value shortdescription longdescription datetime id
buyer smith 123
seller jones 123
selldate 1/1/2011 123
location New York 123
is there a way to get my data out like this instead?
id buyer seller selldate location
123 smith jones 1/1/2010 New York
is this way possible within my query?

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

The answer to your question is of course "It depends".

Without your table definition(s), sample data (more than what you have posted) it is almost impossible to give you a tested answer.

So please help us to help you.

Click on the first link in my signature block to learn how to post table definition(s), sample data, etc. The article contains sample T-SQL statements to make this task relatively easy.

With the requested information some one will be able to provided you with a tested solution to achieve what you require.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 2

I can't show the table schema and what I have shown is what most of my data looks like.

it contains 5 colums and they are all nvarchar fields, so I don't need any calculations, etc, just convert the rows into comlumns

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

SQL_NuB (3/31/2011)
I can't show the table schema and what I have shown is what most of my data looks like.
it contains 5 colums and they are all nvarchar fields, so I don't need any calculations, etc, just convert the rows into comlumns

The answer to both of your original questions is "Yes". In order to demonstrate how to do it, we're asking for you to setup the test data in the form of a CREATE TABLE statement and some readily consumable data. Please see the first link in my signature below for how to correctly build readily consumable data.

Again... not trying to be difficult here. We just want you to help us help you a little. 😉

--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)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 4

how's this:

CREATE TABLE [dbo].[sales](

[id] [int] IDENTITY(1,1) NOT NULL,

[value] [nvarchar](50) NULL,

[shortDescription] [nvarchar](max) NULL,

[longdescription] [nvarchar](max) NULL,

[datetime] [smalldatetime] NULL

) ON [PRIMARY]

INSERT INTO[dbo].[sales]

([value]

,[shortDescription]

,[longdescription]

,[datetime])

VALUES

('Seller'

,''

,'Jones'

,3/31/2010)

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

Much better. Do you think you could squeeze out more than a single row of data, though? 😉

--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)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 6

@jeff,

I have it about 99.9% working, but i'm losing my date, this is what I have

CREATE TABLE [dbo].[sales](

[id] [int] IDENTITY(1,1) NOT NULL,

[value] [nvarchar](50) NULL,

[shortDescription] [nvarchar](max) NULL,

[longdescription] [nvarchar](max) NULL,

[datetime] [smalldatetime] NULL

) ON [PRIMARY]

select id,

max(case when value like 'make' then shortdescription else '' end) as carMake,

max(case when value like 'model' then shortdescription else '' end) as model,

max(case when value like 'SellDate' then DateValue else '' end) as SoldDate

from sales

where location = 'NE'

group by ID,

this query works and returns my date field correct as 3/31/2011, however when I put this in my where clause, where location = 'NE' and type = 'Used'

My date SoldDate field is 1900-01-01 00:00:00.000

why is my date field coming back like that when I ad my type in the where clause? I need to break it out this way so it shows correclty on my web page.

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 7

I got it working

I did this:

select * from

(select id,

max(case when value like 'make' then shortdescription else '' end) as carMake,

max(case when value like 'model' then shortdescription else '' end) as model,

max(case when value like 'SellDate' then DateValue else '' end) as SoldDate

from sales

where location = 'NE'

group by ID

) x

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

SQL_NuB (3/31/2011)
I got it working
I did this:
select * from
(select id,
max(case when value like 'make' then shortdescription else '' end) as carMake,
max(case when value like 'model' then shortdescription else '' end) as model,
max(case when value like 'SellDate' then DateValue else '' end) as SoldDate
from sales
where location = 'NE'
group by ID
) x
That's exactly correct. That method is called a "Cross Tab".

--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)

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 9

@jeff, thanks for your help on this. the more and more i posted the more it was coming to me, so I guess posting was like me talking it out - out loud. 🙂 thanks