May 12, 2010 at 10:56 pm
I have table with values of 62,55,0,67. Is there away to arrange the data in table to be in 0,55,62,67. I need to put the values in order of smallest to largest. I have over 959 rows that i have to do this own.I cant figure it out?
col1 col2 col3 col4
-------- --------- ---------- -----------
65 55 0 67
1 3 2 5
Results:
col1 col2 col3 col4
-------- --------- ---------- -----------
0 55 65 67
1 2 3 5
May 12, 2010 at 11:35 pm
This should give you the expected result
DECLARE @tblTable TABLE
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 INT
)
INSERT @tblTable
SELECT 65,55,0,67 UNION ALL
SELECT 1,3,2,5 UNION ALL
SELECT 24, 21, 3, 19
; WITH cte_Table AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY Col1 ) ID, *
FROM @tblTable
), cte_Table_1 AS
(
SELECT ID, Col, Val
FROM cte_Table T
UNPIVOT ( Val FOR Col IN ( Col1, Col2, Col3, Col4 ) ) Un
), cte_Table_2 AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Val ) RowNum, ID, Val
FROM cte_Table_1
)
SELECT ID, MIN([1]) Col1, MIN([2]) Col2, MIN([3]) Col3, MIN([4]) Col4
FROM cte_Table_2 T2
PIVOT( SUM( Val ) FOR RowNum IN ( [1], [2], [3], [4] ) ) P
GROUP BY ID
But if you want to code the solution for some stored procedure, i would advise you to create a Table Valued Function which would take 4 paramters and then you can use a CROSS APPLY
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 13, 2010 at 6:40 am
Hi:
I not sure how that works. I have over 959 rows of data. I saw that you mention i could use a function that did the same thing. I didn't quite understand what you had posted and how it works. I assume i would replace those numbers with the columns names?
May 13, 2010 at 6:44 am
This one is really a good solution. Thanks:-)
May 13, 2010 at 7:02 am
h2sut (5/13/2010)
Hi:I not sure how that works. I have over 959 rows of data. I saw that you mention i could use a function that did the same thing. I didn't quite understand what you had posted and how it works. I assume i would replace those numbers with the columns names?
In my example i have assumed the name of the table to be @Table and the column names to be Col1, Col2, etc. All you have to do is replace these with correct names.
And if you are unable to understand the query you can have a look at PIVOT, UNPIVOT, ROW_NUMBER() and Common Table Expressions in BOL. Once you understand these terms, you will be able to understand the given query.
And the function method that i talked about will have a query like this
SELECT Fun.*
FROM @tblTable T
CROSS APPLY dbo.udf_GetSortedData( Col1, Col2, Col3, Col4 ) Fun
where dbo.udf_GetSortedData would be a function that would return a sorted row taking four input parameters
And this would be a sample function.
CREATE FUNCTION dbo.udf_GetSortedData
(
@Col1 INT,
@Col2 INT,
@Col3 INT,
@Col4 INT
)
RETURNS @Table TABLE( Col1 INT, Col2 INT, Col3 INT, Col4 INT )
AS
BEGIN
INSERT @Table
SELECT MIN([1]) Col1, MIN([2]) Col2, MIN([3]) Col3, MIN([4]) Col4
FROM (
SELECT ROW_NUMBER() OVER( ORDER BY Col ) Row, Col
FROM (
SELECT @Col1 Col UNION ALL
SELECT @Col2 UNION ALL
SELECT @Col3 UNION ALL
SELECT @Col4
) T
) T
PIVOT( SUM( Col ) FOR Row IN ( [1], [2], [3], [4] ) ) P
RETURN
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 13, 2010 at 12:56 pm
Hello: This worked!! I really appreciate your help on this i cant thank you a enough. Thanks again
be bless
🙂
May 13, 2010 at 12:57 pm
Hello: This worked!! I really appreciate your help on this i cant thank you a enough. Thanks again
be bless
May 13, 2010 at 2:11 pm
h2sut (5/13/2010)
Hello: This worked!! I really appreciate your help on this i cant thank you a enough. Thanks againbe bless
I would caution you: if you can't explain what's happening in this code, do NOT put it into your production system.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 13, 2010 at 10:35 pm
WayneS (5/13/2010)
h2sut (5/13/2010)
Hello: This worked!! I really appreciate your help on this i cant thank you a enough. Thanks againbe bless
I would caution you: if you can't explain what's happening in this code, do NOT put it into your production system.
I would second what Wayne said. Do NOT put this in your production if you don't understand the code. Look up the terms like PIVOT, UNPIVOT, ROW_NUMBER() and CROSS APPLY in BOL. You will get a lot of good articles over the internet too.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply