Another sql query

Question

Post reply

Another sql query

sqldba11

Valued Member

Points: 65
More actions
July 9, 2012 at 9:39 am

#263971

I have a table with the following data in 1 column
part 1
region 10
area 7
part 2
region 11
area 8
The 3 corresponding rows need to be part of a single row/ multiple columns. Please advise on how this can be achieved
part 1 region 10 area 7
part 2 region 11 area 8
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

How to identify/ascertain that the following belogns to Row 1

part 1

region 10

area 7

and the following belongs to Row 2

part 2

region 11

area 8

??

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 2

Can you post the full table definition? If that one column is all that's in the table there's no reliable way to do this. I'm making the assumption that the part is followed in the table with the region and area that belong with it. Since SQL doesn't guarantee a return order short of an order by there's no way to do this without either something to link them, like what ColdCoffee is trying to get at, or an identity column to guarantee order.

The best piece of advice I can give you with this, assuming it's possible, is to split those up so they're not all stored in the same column. If you can store them in three different columns in the same row they're going to be much, much easier to work with.

sqldba11 Valued Member Points: 65 More actions · Answer 3

Thanks for your response. There is an identity column, and the first 3 are corresponding data.

ie, rows with identity values 1,2,3 will need to become columns 1,2,3 of row 1

4,5,6 will then become row 2, those with 7,8,9 will become row 3.

Thank you

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

SELECT

(ident - 1) % 3,

MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part,

MAX(CASE WHEN (ident - 1) % 3 = 1 THEN value ELSE 0 END) AS region,

MAX(CASE WHEN (ident - 1) % 3 = 2 THEN value ELSE 0 END) AS area

FROM dbo.tablename

GROUP BY

(ident - 1) % 3

--ORDER BY ident

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

ScottPletcher (7/9/2012)
SELECT (ident - 1) % 3, MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part, MAX(CASE WHEN (ident - 1) % 3 = 1 THEN value ELSE 0 END) AS region, MAX(CASE WHEN (ident - 1) % 3 = 2 THEN value ELSE 0 END) AS area FROM dbo.tablename GROUP BY (ident - 1) % 3 --ORDER BY ident

The only problem with that is that you cannot guarantee that the IDENTITY column has no gaps. I recommend adding a ROW_NUMBER() column sorted by the IDENTITY column.

--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)
Intro to Tally Tables and Functions

ScottPletcher SSC Guru Points: 100262 More actions · Answer 6

Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT (ident - 1) % 3, MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part, MAX(CASE WHEN (ident - 1) % 3 = 1 THEN value ELSE 0 END) AS region, MAX(CASE WHEN (ident - 1) % 3 = 2 THEN value ELSE 0 END) AS area FROM dbo.tablename GROUP BY (ident - 1) % 3 --ORDER BY ident
The only problem with that is that you cannot guarantee that the IDENTITY column has no gaps. I recommend adding a ROW_NUMBER() column sorted by the IDENTITY column.

Could verify it first:

SELECT MIN($IDENTITY), MAX($IDENTITY), COUNT(*)

FROM dbo.tablename

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 7

ScottPletcher (7/10/2012)
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT (ident - 1) % 3, MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part, MAX(CASE WHEN (ident - 1) % 3 = 1 THEN value ELSE 0 END) AS region, MAX(CASE WHEN (ident - 1) % 3 = 2 THEN value ELSE 0 END) AS area FROM dbo.tablename GROUP BY (ident - 1) % 3 --ORDER BY ident
The only problem with that is that you cannot guarantee that the IDENTITY column has no gaps. I recommend adding a ROW_NUMBER() column sorted by the IDENTITY column.
Could verify it first:
SELECT MIN($IDENTITY), MAX($IDENTITY), COUNT(*)
FROM dbo.tablename

And then do what? It may be possible to update it but I don't know that I would recommend it. And using row_number shouldn't add much overhead.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 8

cfradenburg (7/10/2012)
ScottPletcher (7/10/2012)
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT (ident - 1) % 3, MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part, MAX(CASE WHEN (ident - 1) % 3 = 1 THEN value ELSE 0 END) AS region, MAX(CASE WHEN (ident - 1) % 3 = 2 THEN value ELSE 0 END) AS area FROM dbo.tablename GROUP BY (ident - 1) % 3 --ORDER BY ident
The only problem with that is that you cannot guarantee that the IDENTITY column has no gaps. I recommend adding a ROW_NUMBER() column sorted by the IDENTITY column.
Could verify it first:
SELECT MIN($IDENTITY), MAX($IDENTITY), COUNT(*)
FROM dbo.tablename
And then do what? It may be possible to update it but I don't know that I would recommend it. And using row_number shouldn't add much overhead.

If it checks out, you can run the query as is.

If it doesn't, you have to use the ROW_NUMBER().

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!