November 20, 2013 at 5:10 pm
In the world of dynamically resizing HTML tables according to the number of elements, I have to believe there's a more elegant way of accomplishing this
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test (id NUMERIC IDENTITY(1,1) PRIMARY KEY, value VARCHAR(MAX))
INSERT INTO #test (value) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k')
/* Make two equal columns */
WITH detail AS
(SELECT
id,
Value,
ROUND((ROW_NUMBER() OVER (ORDER BY id) + 1) / 2,0,0) NewRow,
ROW_NUMBER() OVER (ORDER BY id) % 2 AS OddEven
FROM
#test)
SELECT
NewRow,
MAX(CASE WHEN OddEven = 1 THEN Value ELSE '' END) Column1,
MAX(CASE WHEN OddEven = 0 THEN Value ELSE '' END) Column2
FROM
detail
GROUP BY
NewRow
ORDER BY
NewRow
NewRowColumn1Column2
1ab
2cd
3ef
4gh
5ij
6k
November 20, 2013 at 5:28 pm
It doesn't get much more elegant than that, but there are multiple ways to skin that same cat.
SELECT NewRow=b.ID
,Col1=MAX(CASE val WHEN 1 THEN Value ELSE '' END)
,Col2=MAX(CASE val WHEN 0 THEN Value ELSE '' END)
FROM #test a
CROSS APPLY (SELECT val=ASCII(value)%2, ID=(CAST(ID AS INT)+1)/2) b
GROUP BY b.ID
ORDER BY b.ID;
Whatever floats your boat!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 21, 2013 at 11:04 am
You don't have to use two ROW_NUMBER() clauses/entries:
;WITH detail AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM #test
)
SELECT
(row_num + 1) / 2 AS NewRow,
MAX(CASE WHEN row_num % 2 = 1 THEN value ELSE '' END) AS Column1,
MAX(CASE WHEN row_num % 2 = 0 THEN value ELSE '' END) AS Column2
FROM detail
GROUP BY
(row_num + 1) / 2
ORDER BY
NewRow
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 21, 2013 at 12:43 pm
Same execution plan, but your code has benefit of being easier and more logical to adapt to 3 columns:
I'll leave it as a homework assignment to myself to allow it to adapt to "n" columns ...
;WITH detail AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM #test
)
SELECT
(row_num + 2) / 3 AS NewRow,
MAX(CASE WHEN row_num % 3 = 1 THEN value ELSE '' END) AS Column1,
MAX(CASE WHEN row_num % 3 = 2 THEN value ELSE '' END) AS Column2,
MAX(CASE WHEN row_num % 3 = 0 THEN value ELSE '' END) AS Column3
FROM detail
GROUP BY
(row_num + 2) / 3
ORDER BY
NewRow
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply