July 5, 2012 at 4:52 am
Hi,
I need to show the rows of a query into three columns like this:
Original table:
field1
====
Name1
Name2
Name3
Name4
Name5
...
Result:
Field1 Field2 Field3
===============
Name1 Name2 Name3
Name4 Name5 ...
...
What SQL statement or whet procedure do I need?
Can anyone help me?
July 5, 2012 at 6:15 am
What determines whether a value goes into Column1, Column2, or Column3?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 5, 2012 at 6:30 am
Each 3 rows in original table go to one row in the new view.
E.g:
Row1,Row2 and Row3 from original table go to Column1, Column2 and Column3 in the first row of the result view
Row4,Row5 and Row6 from original table go to Column1, Column2 and Column3 in the second row of the result view
...
July 5, 2012 at 7:01 am
Javier-157657 (7/5/2012)
Each 3 rows in original table go to one row in the new view.E.g:
Row1,Row2 and Row3 from original table go to Column1, Column2 and Column3 in the first row of the result view
Row4,Row5 and Row6 from original table go to Column1, Column2 and Column3 in the second row of the result view
...
Interesting and surely time taking.Will try this once have time.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 5, 2012 at 11:38 am
rhythmk (7/5/2012)
Javier-157657 (7/5/2012)
Each 3 rows in original table go to one row in the new view.E.g:
Row1,Row2 and Row3 from original table go to Column1, Column2 and Column3 in the first row of the result view
Row4,Row5 and Row6 from original table go to Column1, Column2 and Column3 in the second row of the result view
...
Interesting and surely time taking.Will try this once have time.
It takes very little time. However, there is NO guaranteed order to anything stored in a table unless you do an ORDER BY on a column. Do you have an additional column that quaranttes the order you want? Perhaps an identity or date column???
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2012 at 10:47 pm
Jeff Moden (7/5/2012)
rhythmk (7/5/2012)
Javier-157657 (7/5/2012)
Each 3 rows in original table go to one row in the new view.E.g:
Row1,Row2 and Row3 from original table go to Column1, Column2 and Column3 in the first row of the result view
Row4,Row5 and Row6 from original table go to Column1, Column2 and Column3 in the second row of the result view
...
Interesting and surely time taking.Will try this once have time.
It takes very little time. However, there is NO guaranteed order to anything stored in a table unless you do an ORDER BY on a column. Do you have an additional column that quaranttes the order you want? Perhaps an identity or date column???
hmm.....something very easy for you but tricky for me 😉
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 6, 2012 at 12:32 am
Jeff Moden (7/5/2012)
rhythmk (7/5/2012)
Javier-157657 (7/5/2012)
Each 3 rows in original table go to one row in the new view.E.g:
Row1,Row2 and Row3 from original table go to Column1, Column2 and Column3 in the first row of the result view
Row4,Row5 and Row6 from original table go to Column1, Column2 and Column3 in the second row of the result view
...
Interesting and surely time taking.Will try this once have time.
It takes very little time. However, there is NO guaranteed order to anything stored in a table unless you do an ORDER BY on a column. Do you have an additional column that quaranttes the order you want? Perhaps an identity or date column???
Here is the information you need:
ORDER BY will be established from Table1.Column1
TABLE1
=====
Column1
----------
John
Alex
James
Bob
Mike
Louis
...
SELECT Column1 FROM Table1 ORDER BY Column1
RESULT
=====
Column1 Column2 Column3
----------------------------------
Alex Bob James
John Louis Mike
...
July 6, 2012 at 6:42 am
{EDIT} My apologies. I just noticed that this is an SQL Server 2000 forum and the code above won't work in 2k. I'll be back.
Take a look at how I created the test data below. It'll help you get coded answers much quicker in the future if you post your data that way because most folks on this forum like to test their answers before they post. Anything you can do to make that easier for them will entice them to answering more quickly.
Here's the whole shootin' match... test data and one possible solution. I added an extra name to show it works even for numbers of rrows not evenly divisible by 3. It's called a "Cross Tab" which is like a PIVOT but generally a touch faster. See the following article for more information on converting rows to columns. http://www.sqlservercentral.com/articles/T-SQL/63681/
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Table1','U') IS NOT NULL DROP TABLE #Table1;
--===== Create the test table
CREATE TABLE #Table1
(Column1 VARCHAR (20))
;
--===== Populate the test table with test data.
INSERT INTO #Table1
(Column1)
SELECT 'John' UNION ALL
SELECT 'Alex' UNION ALL
SELECT 'James' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Mike' UNION ALL
SELECT 'Louis'
;
--===== Solve the problem with a bit of row and element math.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Column1)-1,
Column1
FROM #Table1
)
SELECT MAX(CASE WHEN RowNum%3 = 0 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 2 THEN Column1 ELSE '' END)
FROM cteEnumerate
GROUP BY RowNum/3
ORDER BY RowNum/3
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 6:49 am
Here we go. This will do the trick in SQL Server 2000.
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Table1','U') IS NOT NULL DROP TABLE #Table1;
--===== Create the test table
CREATE TABLE #Table1
(Column1 VARCHAR (20))
;
--===== Populate the test table with test data.
INSERT INTO #Table1
(Column1)
SELECT 'John' UNION ALL
SELECT 'Alex' UNION ALL
SELECT 'James' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Mike' UNION ALL
SELECT 'Louis'
;
--===== Since this is for SQL Server 2000 and we can't take
-- advantage of the ROW_NUMBER() function, we have to
-- use a different method to enumerate the rows.
IF OBJECT_ID('tempdb..#Enumerate','U') IS NOT NULL
DROP TABLE #Enumerate
;
SELECT RowNum = IDENTITY(INT,0,1),
Column1
INTO #Enumerate
FROM #Table1
ORDER BY Column1
OPTION (MAXDOP 1)
;
--===== Solve the problem with a bit of row and element math.
SELECT MAX(CASE WHEN RowNum%3 = 0 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 2 THEN Column1 ELSE '' END)
FROM #Enumerate
GROUP BY RowNum/3
ORDER BY RowNum/3
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 6:55 am
Jeff Moden (7/6/2012)
Here we go. This will do the trick in SQL Server 2000.
--===== Conditionally drop the test table to make reruns in SSMS easier.IF OBJECT_ID('tempdb..#Table1','U') IS NOT NULL DROP TABLE #Table1;
--===== Create the test table
CREATE TABLE #Table1
(Column1 VARCHAR (20))
;
--===== Populate the test table with test data.
INSERT INTO #Table1
(Column1)
SELECT 'John' UNION ALL
SELECT 'Alex' UNION ALL
SELECT 'James' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Mike' UNION ALL
SELECT 'Louis'
;
--===== Since this is for SQL Server 2000 and we can't take
-- advantage of the ROW_NUMBER() function, we have to
-- use a different method to enumerate the rows.
IF OBJECT_ID('tempdb..#Enumerate','U') IS NOT NULL
DROP TABLE #Enumerate
;
SELECT RowNum = IDENTITY(INT,0,1),
Column1
INTO #Enumerate
FROM #Table1
ORDER BY Column1
OPTION (MAXDOP 1)
;
--===== Solve the problem with a bit of row and element math.
SELECT MAX(CASE WHEN RowNum%3 = 0 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 2 THEN Column1 ELSE '' END)
FROM #Enumerate
GROUP BY RowNum/3
ORDER BY RowNum/3
;
Or, if we know that the "Column1" is unique we could do a subquery like this: -
SELECT MAX(CASE WHEN RowNum % 3 = 0 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum % 3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum % 3 = 2 THEN Column1 ELSE '' END)
FROM (SELECT RowNum = (SELECT COUNT(*) FROM #Table1 a WHERE a.Column1 <= b.Column1) - 1,
Column1
FROM #Table1 b) a
GROUP BY RowNum / 3
ORDER BY RowNum / 3;
July 6, 2012 at 7:36 am
Cadavre (7/6/2012)
Or, if we know that the "Column1" is unique we could do a subquery like this: -
SELECT MAX(CASE WHEN RowNum % 3 = 0 THEN Column1 ELSE '' END),MAX(CASE WHEN RowNum % 3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum % 3 = 2 THEN Column1 ELSE '' END)
FROM (SELECT RowNum = (SELECT COUNT(*) FROM #Table1 a WHERE a.Column1 <= b.Column1) - 1,
Column1
FROM #Table1 b) a
GROUP BY RowNum / 3
ORDER BY RowNum / 3;
Ohhhh... be careful now. That's a "Triangular Join" and won't scale worth a hoot. Please see the following article for what I mean. It can be a real server killer.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 8:10 am
Jeff Moden (7/6/2012)
Cadavre (7/6/2012)
Or, if we know that the "Column1" is unique we could do a subquery like this: -
SELECT MAX(CASE WHEN RowNum % 3 = 0 THEN Column1 ELSE '' END),MAX(CASE WHEN RowNum % 3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum % 3 = 2 THEN Column1 ELSE '' END)
FROM (SELECT RowNum = (SELECT COUNT(*) FROM #Table1 a WHERE a.Column1 <= b.Column1) - 1,
Column1
FROM #Table1 b) a
GROUP BY RowNum / 3
ORDER BY RowNum / 3;
Ohhhh... be careful now. That's a "Triangular Join" and won't scale worth a hoot. Please see the following article for what I mean. It can be a real server killer.
Most definitely, and the divide and conquer method of spitting it into a temporary table is always going to be better. However, I did have a situation some months ago where I was forced to enumerate a table in SQL Server 2000 where I wasn't allowed to create objects (even temporary ones). I won't bore you with the details, but suffice it to say that it was a very irritating discussion that ended up with the subquery above.
--edit--
Could've done with that link when I was trying to explain the potential pitfalls. . . going to have to save it somewhere.
July 9, 2012 at 12:34 am
Thank you very much. It is very useful for me.
As you said, use of a temporary table is better and more efficient.
I have tried your two solutions and it is better when I used a temporary table.
Definitely:
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Table1','U') IS NOT NULL DROP TABLE #Table1;
--===== Create the test table
CREATE TABLE #Table1
(Column1 VARCHAR (20))
;
--===== Populate the test table with test data.
INSERT INTO #Table1
(Column1)
SELECT 'John' UNION ALL
SELECT 'Alex' UNION ALL
SELECT 'James' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Mike' UNION ALL
SELECT 'Louis'
;
--===== Since this is for SQL Server 2000 and we can't take
-- advantage of the ROW_NUMBER() function, we have to
-- use a different method to enumerate the rows.
IF OBJECT_ID('tempdb..#Enumerate','U') IS NOT NULL
DROP TABLE #Enumerate
;
SELECT RowNum = IDENTITY(INT,0,1),
Column1
INTO #Enumerate
FROM #Table1
ORDER BY Column1
OPTION (MAXDOP 1)
;
--===== Solve the problem with a bit of row and element math.
SELECT MAX(CASE WHEN RowNum%3 = 0 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 1 THEN Column1 ELSE '' END),
MAX(CASE WHEN RowNum%3 = 2 THEN Column1 ELSE '' END)
FROM #Enumerate
GROUP BY RowNum/3
ORDER BY RowNum/3
;
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply