June 20, 2010 at 12:50 pm
Hi all!
I have a table as follows:
ID Name
1 fname1
2 fname2
5 fname3
6 fname7
7 fname8
8 fname9
9 fname20
What i need is a query which returns the same table, but with doubled columns:
ID Name ID2 Name2
1 fname1 2 fname2
5 fname3 6 fname7
7 fname8 8 fname9
9 fname20
As you can see if the total number of rows is odd, it should populate only the first 2 columns.
It's very easy to do this using cursor, but i'm curious to know if it's possible to do it using sets.
Thank you!
June 20, 2010 at 12:59 pm
Renis yes it is certainly possible;
check out this example; it is getting the names of all the tables and putting it into five columns.
in your case, you just need to change it so that the 5 in the calculation is a 2, and remove the CASE statements which would produce cols 3/4/5:
WITH
baseCTE AS
(
SELECT
(ROW_NUMBER() OVER (ORDER BY Name)-1)/5+1 AS RW, --change the 2 for 2 cols
(ROW_NUMBER() OVER (ORDER BY Name)-1)%5+1 AS CL, --change the 2 for 2 cols
Name
FROM sys.tables
)
SELECT MAX(CASE WHEN CL = 1 THEN Name ELSE '' END) AS Col1TableName,
MAX(CASE WHEN CL = 2 THEN Name ELSE '' END) AS Col2TableName,
MAX(CASE WHEN CL = 3 THEN Name ELSE '' END) AS Col3TableName, --remove for 2 cols
MAX(CASE WHEN CL = 4 THEN Name ELSE '' END) AS Col4TableName,--remove for 2 cols
MAX(CASE WHEN CL = 5 THEN Name ELSE '' END) AS Col5TableName--remove for 2 cols
FROM baseCTE
GROUP BY RW
;
Lowell
June 21, 2010 at 3:16 am
Thank's mate! Perfect solution!
June 21, 2010 at 10:02 am
Maybe this will work for you
WITH P (RN,ID,[NAME]) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY ID)
, ID
, [NAME] FROM MYTABLE
)
SELECT
T1.ID,T1.[NAME], T2.ID,T2.[NAME]
FROM
P T1 LEFT JOIN P T2 ON T1.RN = T2.RN-1
WHERE
T1.RN % 2 =1
June 21, 2010 at 1:46 pm
And another variation, this time using PIVOT
IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t
SELECT 1 AS id, 'fname1' AS name INTO #t
UNION ALL SELECT 2, 'fname2'
UNION ALL SELECT 5, 'fname3'
UNION ALL SELECT 6, 'fname7'
UNION ALL SELECT 7, 'fname8'
UNION ALL SELECT 8, 'fname9'
UNION ALL SELECT 9, 'fname20'
;
WITH cteprepare
AS (SELECT ( Row_number() OVER (ORDER BY id) - 1 ) / 2 AS BLOCK,
Row_number() OVER (ORDER BY id) % 2 AS comp,
id,
name
FROM #t),
ctepivot
AS (SELECT BLOCK,
id,
[1] AS name,
[0] AS name2
FROM cteprepare PIVOT (MAX(name) FOR comp IN ([1], [0]) ) AS z)
SELECT MIN(id) AS id,
MAX(name) AS name,
Nullif(MAX(id), MIN(id)) AS id2,
MAX(name2) AS name2
FROM ctepivot
GROUP BY BLOCK
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply