July 9, 2012 at 9:39 am
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
July 9, 2012 at 9:43 am
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
??
July 9, 2012 at 12:58 pm
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.
July 9, 2012 at 4:35 pm
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
July 9, 2012 at 4:41 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 9, 2012 at 8:00 pm
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
Change is inevitable... Change for the better is not.
July 10, 2012 at 8:33 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 10, 2012 at 8:56 am
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.
July 10, 2012 at 11:11 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply