June 29, 2009 at 6:02 am
Hi ALL
I have a table and I need both horizontal and vertical sorting for the table
Example:-
DECLARE @t TABLE ( c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))
insert into @t (c1, c2, c3) values ('2','1','3')
insert into @t (c1, c2, c3) values ('3','2','1')
insert into @t (c1, c2, c3) values ('Z','X','Y')
insert into @t (c1, c2, c3) values ('B','C','D')
insert into @t (c1, c2, c3) values ('Y','Z','X')
insert into @t (c1, c2, c3) values ('B','C','A')
SELECT * FROM @t
/* Expected Result
C1 C2 C3
--- --- ---
1 2 3
A B C
B C D
X Y Z
*/
Pls Suggest me a query for above requirement
Thanks in advance 🙂
June 29, 2009 at 7:39 am
Each row is one record. Therefore:
a) If you sort the rows under c1, it is impossible to also sort c2 and c3. This is even if you include all three in the order by clause. This is because c1 will have the first sort priority.
b) Even if you were able to order the columns horizontally by using some sub-query, you cannot change the order for the next record. Therefore it would be impossible to sort horizontally.
Eli
June 29, 2009 at 7:42 am
he can use a case statement to group them horizontally;
well...three case statements, right?
case
when c1 <= c2 and c1 c1 and c2 <= c3 then c2....
Lowell
June 29, 2009 at 7:53 am
Unless c1, c2, c3.. are intrinsically meaningless and are just data placeholders, then the case statement will just help the first record. Subsequent records will have to follow the determined order for the first record and will be entirely out of order.
June 29, 2009 at 2:07 pm
espilman (6/29/2009)
Unless c1, c2, c3.. are intrinsically meaningless and are just data placeholders, then the case statement will just help the first record. Subsequent records will have to follow the determined order for the first record and will be entirely out of order.
I disagree. The CASE statement treats each row separately. So it's possible to horizontally order the values. The tricky part is to come up with the right CASE statement to cover all possible circumstances. But it's doable. (I'm not done with testing yet...)
June 29, 2009 at 2:22 pm
Ok, I'm done with testing and I think I covered all possible occurrences (except NULL values since there is no business rule on how to handle such values...).
I used the sample data you provided and the basic idea Lowell suggested:
-- step 1: get the values per row ordered
;WITH cte_order
AS
(
SELECT
CASE
WHEN c1 <= c2 AND c1 <= c3 THEN c1
WHEN c2 <= c1 AND c2 = c2 AND c1 = c3 AND c1 = c1 AND c2 = c3 AND c2 = c1 AND c3 = c2 AND c3 = c2 AND c1 >= c3 THEN c1
WHEN c2 >= c1 AND c2 >= c3 THEN c2
ELSE c3 END
AS ThirdVal
FROM @t
)
-- step 2: select the aggregated result set
SELECT FirstVal,SecondVal,ThirdVal
FROM cte_order
GROUP BY FirstVal,SecondVal,ThirdVal
/* result set
FirstValSecondValThirdVal
123
ABC
BCD
XYZ
*/
June 29, 2009 at 2:28 pm
I know that a CASE statement treats each row separately -- that's part of the problem. Once the collumns are ordered, using the first given examples you will end up with something like this:
c2 c1 c3
c1 c2 c3
c2 c3 c1
The columns don't correlate with each other anymore. This is only ok if the columns are meaningless.
June 29, 2009 at 2:48 pm
My first response was based on your statement:
espilman (6/29/2009)
... then the case statement will just help the first record. Subsequent records will have to follow the determined order for the first record and will be entirely out of order.
Using the code I posted, I can't see any "out of order" results for subsequent rows in the result set.
You're right when it comes to the order of the original columns: those will be mixed. But isn't that exactly what the OP asked for?
On the other side I can't figure out how you'd use a CASE statement on the first record and have the subsequent rows following that order - especially, since it's hard to know which row would have to be considered as being first in the given example. Would you mind showing how you'd do that? (just being curious)
June 29, 2009 at 7:33 pm
mandirkumar (6/29/2009)
Hi ALLI have a table and I need both horizontal and vertical sorting for the table
Example:-
What is the business requirement behind doing this? It may be important to the actual answer which can be done in a very simple manner depending on the business requirement. Why must the data be displayed as whole but reorganized rows?
--Jeff Moden
June 29, 2009 at 8:12 pm
[font="Arial Black"]I still want to know the business requirement behind all this, please.[/font]
Ok... here's a very simple answer especially if you have more than 3 columns that you need to sort horizontally. It could very easily be turned into a bit of dynamic SQL to automatically read the column names from the table.
[font="Courier New"]--===== Create and populate the test data table.
-- This is not part of the solution
IF OBJECT_ID('TempDB..#t','U') IS NOT NULL DROP TABLE #t
CREATE TABLE #t (c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))
INSERT INTO #t (c1, c2, c3)
SELECT '2','1','3' UNION ALL
SELECT '3','2','1' UNION ALL
SELECT 'Z','X','Y' UNION ALL
SELECT 'B','C','D' UNION ALL
SELECT 'Y','Z','X' UNION ALL
SELECT 'B','C','A'
--===== Produce the required output using a couple of tricks from 2k5 and the
-- "Book of Boy Howdy" to get the job done. This greatly simplifies the
-- problems associated with horizontal sorts. It's also relatively easy
-- to add additional columns without having to add additional and very
-- intense Case statements to do the horizontal ordering.
;WITH
cteHorzSort AS
(--==== "Stands the data up" in an NVP table with a ranked column for the HorzSortOrder
SELECT RowNum, ColName, ColValue, --ColName not actually required but left in for understanding
ROW_NUMBER() OVER (PARTITION BY RowNum ORDER BY RowNum, ColValue) AS HorzSortOrder
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1,c2,c3) AS RowNum, c1,c2,c3 FROM #t) p
UNPIVOT (ColValue FOR ColName IN (c1,c2,c3)) AS nvp
)--==== Nice, fast, easy to understand Cross-Tab to put it all back together
-- and to do the final vertical sort.
SELECT MAX(CASE WHEN HorzSortOrder = 1 THEN ColValue END) AS c1,
MAX(CASE WHEN HorzSortOrder = 2 THEN ColValue END) AS c2,
MAX(CASE WHEN HorzSortOrder = 3 THEN ColValue END) AS c3
FROM cteHorzSort
GROUP BY RowNum
ORDER BY c1
[/font]
________________________________________________________________________________________________________________
--Jeff Moden
June 30, 2009 at 12:56 am
Nice one, Jeff!!
Sometimes the occurrence of the word "PIVOT" isn't that bad, obviously... - especially when it comes together with an "UN".
Seems a little easier to deal with than the CASE stuff (once one would have figured out the UNPIVOT syntax, of course... 😉 )
June 30, 2009 at 3:57 am
Thanks to everyone 🙂
June 30, 2009 at 5:57 am
mandirkumar (6/30/2009)
Thanks to everyone 🙂
Oh no, no, no... you can't go away, yet. I want to know what the business requirements are that caused you to ask this question, please.
--Jeff Moden
June 30, 2009 at 6:00 am
lmu92 (6/30/2009)
Nice one, Jeff!!Sometimes the occurrence of the word "PIVOT" isn't that bad, obviously... - especially when it comes together with an "UN".
Seems a little easier to deal with than the CASE stuff (once one would have figured out the UNPIVOT syntax, of course... 😉 )
Heh... nah... the word PIVOT is always bad even when it's in the form of UNPIVOT. I didn't post what I thought the optimal solution would be with an "UN"Cross-Tab because I want the OP to tell me what the business reason for this would be and didn't want to spend much time on a possible solution unless he did. 😉
We'll see what happens but notice that the OP has, so far, ignored my request.
--Jeff Moden
June 30, 2009 at 6:09 am
lmu92 (6/29/2009)... Would you mind showing how you'd do that? (just being curious)
I definitely wouldn't have come up with Jeff's solution :-D. All I was saying is that I wasn't sure whether the integrity of the columns needed to be intact.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply