June 27, 2011 at 11:05 am
EDITED: and going further with Ninja's approach ...
;WITH
ListWithRows
AS (SELECT
(ROW_NUMBER() OVER (ORDER BY id)/5)+1 as rowNumber
,(ROW_NUMBER() OVER (ORDER BY id)%5)+1 as colNumber
,ID
FROM @Products)
SELECT
rowNumber
,[1]
,[2]
,[3]
,[4]
,[5]
FROM
(SELECT
rowNumber
,colNumber
,ID
FROM
ListWithRows) as pivotSource
PIVOT
(MAX(id) FOR colNumber
IN ([1],[2],[3],[4],[5])) as pivotResult
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 27, 2011 at 12:44 pm
crackbridge, kindly let us know if our solutions have answered your question.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 27, 2011 at 12:53 pm
crackbridge (6/27/2011)
Look friends, I'm sorry if someone felt wrong it was just in save of time and richness of knowledge.I'll be shorter, what I say, forum is not to post too many ways of form but content. Issue was pretty clear, and long paragraphs of philosophy and history, give nothing.
Wrong. You're not the only person who will read this thread. Ever hear of Google? It "finds" things based on keywords, and then sends people down rabbit holes who do not know any better. Not to mention that not everyone who posts is not on a journey to find the next great rabbit hole. Me thinks you know the difference, and me hopes you got your answer despite your demeanor. We pontificate because we care 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 4, 2011 at 6:14 am
Hello friends, I'm sorry I have been off, thanks a lot Selburg, to you and Ninja's who was also in touch with tech approaches.
Yes Selburg, your solution works pretty good, actually I did as you and then I found your post, and found myself proud to get the same.
I would also like to present you today the solution I did to show columns in Top-Bottom fashion, instead of Left-Right.
Kind Regards,
L.
----------------------------------------------------------------------
--Declaring Table and feeding Rows
----------------------------------------------------------------------
DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO @Products(ID)
SELECT '04701' UNION ALL
SELECT '04702' UNION ALL
SELECT '04703' UNION ALL
SELECT '04704' UNION ALL
SELECT '04705' UNION ALL
SELECT '04706' UNION ALL
SELECT '04707' UNION ALL
SELECT '04708' UNION ALL
SELECT '04709' UNION ALL
SELECT '04710' UNION ALL
SELECT '04711' UNION ALL
SELECT '04712' UNION ALL
SELECT '04713' UNION ALL
SELECT '04714' UNION ALL
SELECT '04715' UNION ALL
SELECT '04716' UNION ALL
SELECT '04717' UNION ALL
SELECT '04718' UNION ALL
SELECT '04719' UNION ALL
SELECT '04720';
--------------------------------------------------------------------
-- Building Columns LEFT to RIGHT (Same a Selburg approach)
--------------------------------------------------------------------
WITH cte AS (SELECT ID,
(((ROW_NUMBER() OVER (ORDER BY ID))-1)/5) AS k,
(((ROW_NUMBER() OVER (ORDER BY ID))-1)%5)+1 AS c FROM @Products)
SELECT k, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM cte PIVOT (MAX(id) FOR c IN ([1],[2],[3],[4],[5])) AS pvt
--------------------------------------------------------------------
-- Building Columns TOP to BOTTOM. (NEW)
--------------------------------------------------------------------
DECLARE @Count INT, @MaxRow INT, @LastColumnMaxRow INT, @ChangePoint INT
SELECT @Count = COUNT(*) FROM @Products;
SET @MaxRow = (@Count / 5) + 1
IF @MaxRow > 1
BEGIN
SET @LastColumnMaxRow = (@Count % 5)
SET @ChangePoint = @MaxRow * @LastColumnMaxRow
END
ELSE
BEGIN
SET @LastColumnMaxRow = @Count
SET @ChangePoint = @Count
END;
WITH cte AS (SELECT ID,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY ID) <= @ChangePoint
THEN (((ROW_NUMBER() OVER (ORDER BY ID)) - 1) / @MaxRow) + 1
ELSE (((ROW_NUMBER() OVER (ORDER BY ID)) - @ChangePoint - 1) / (@MaxRow - 1)) + 1 + @LastColumnMaxRow
END AS c,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY ID) <= @ChangePoint THEN
CASE
WHEN @Count > 5 THEN
CASE
WHEN ((ROW_NUMBER() OVER (ORDER BY ID)) % @MaxRow) > 0
THEN (ROW_NUMBER() OVER (ORDER BY ID) % @MaxRow)
ELSE
@MaxRow
END
ELSE 1
END
ELSE
CASE
WHEN (((ROW_NUMBER() OVER (ORDER BY ID)) - @ChangePoint) % (@MaxRow - 1)) > 0
THEN (((ROW_NUMBER() OVER (ORDER BY ID)) - @ChangePoint) % (@MaxRow - 1))
ELSE
@MaxRow - 1
END
END AS k
FROM @Products GROUP BY ID)
SELECT k, [1] AS Column1, [2] AS Column2, [3] AS Column3, [4] AS Column4, [5] AS Column5
FROM cte PIVOT (MAX(id) FOR c IN ([1],[2],[3],[4],[5])) AS pvt
July 4, 2011 at 8:51 am
crackbridge (6/27/2011)
but trust me best practice to help in the forum is do not issue banal views when the answer is not known
Actually, it IS a best practice to "issue banal views" when someone feels that a poster is possibly making a terrible mistake. 😉 The purpose of the forum isn't to be an "instant help" but to discuss the issue at hand to come up with the best solution possible. Of course, that sometimes takes a bit longer and takes a few more replies than what you would like, but it all works out in the end, just as it has for you. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply