May 19, 2010 at 9:28 am
Hi, I want to make a crosstab of some data but based on row counts. So for a simple example, say I had a field with 101 through to 115 in it, and I would like to create a crosstab of every 5 rows, so to end up with a query result as below . . . .
Column1 Column2 Column3 Column4 Column5
101 102 103 104 105
106 107 108 109 110
111 112 113 114 115
Any ideas how to do this ?
May 19, 2010 at 9:59 am
Based on a numbers (or tally) table I'd use the following logic:
;
WITH cte AS
(
SELECT
n,
(ROW_NUMBER() OVER(ORDER BY n)-1) % 5 +1 AS c,
(ROW_NUMBER() OVER(ORDER BY n)-1) / 5 AS r
FROM tally
WHERE n > 100 AND n < 116
)
SELECT
MAX (CASE WHEN c=1 THEN n ELSE NULL END) AS c1,
MAX (CASE WHEN c=2 THEN n ELSE NULL END) AS c2,
MAX (CASE WHEN c=3 THEN n ELSE NULL END) AS c3,
MAX (CASE WHEN c=4 THEN n ELSE NULL END) AS c4,
MAX (CASE WHEN c=5 THEN n ELSE NULL END) AS c5
FROM cte
GROUP BY r
ORDER BY r
May 19, 2010 at 12:55 pm
Here's an approach that is similar to the one Lutz posted. As he did, it's based off of tally table.
Main differences are that you can specify the number of rows to return, and it gets the values out of the tally table based on #Rows * # columns (5). It also uses NTILE() to get the row number.
declare @NumRows INT
set @NumRows = 4
;WITH CTE AS
(
SELECT N,
Row = NTILE(@NumRows) OVER (ORDER BY N),
Col = (ROW_NUMBER() OVER (ORDER BY N))%5
FROM tally
WHERE N > 100 AND N < (101 + (@NumRows * 5))
)
SELECT
MAX (CASE WHEN Col=1 THEN N ELSE NULL END) AS c1,
MAX (CASE WHEN Col=2 THEN N ELSE NULL END) AS c2,
MAX (CASE WHEN Col=3 THEN N ELSE NULL END) AS c3,
MAX (CASE WHEN Col=4 THEN N ELSE NULL END) AS c4,
MAX (CASE WHEN Col=0 THEN N ELSE NULL END) AS c5
FROM CTE
GROUP BY Row
ORDER BY Row
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply