March 19, 2011 at 6:55 am
Hi,
Am having table with two column like follows
+--------------------------+------------------------------+
| COLUMN1 | COLUMN2 |
+--------------------------+------------------------------+
| A | CCC |
| A | AAA |
| A | BBB |
| A | CCC |
| A | BBB |
| A | AAA |
+--------------------------+------------------------------+
OUTPUT :
I need select query to display in following format..
+--------------------------+------------------------------+
| COLUMN1 | COLUMN2 |
+--------------------------+------------------------------+
| A | AAA |
| A | BBB |
| A | CCC |
| A | AAA |
| A | BBB |
| A | CCC |
+--------------------------+------------------------------+
Is this possible..................? please some body help me its very urgent...
March 19, 2011 at 7:20 am
What distinguishes the first 3 rows from the latter 3 rows?
There are ways, but they are odd. What's the reasoning behind this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2011 at 1:05 pm
This is one way to get the result you asked for
SELECT COLUMN1,COLUMN2 FROM MYTABLE
ORDER BY ROW_NUMBER() OVER (PARTITION BY COLUMN1,COLUMN2 ORDER BY COLUMN1,COLUMN2),COLUMN1,COLUMN2
But as Gail said you will need to explain what you are trying to achieve as this may not be what you need.
March 21, 2011 at 12:09 am
Hi Gail..
Thanks for responding..:-)
I need to display the first column in order and second should be displayed as A-Z it should complete the first a.. ,first b... up to z.. and again a.. , b.. up to z..
Original table data is like
Column1 | Column 2
AAA | Apple
AAA | Apple
AAA | Ball
AAA | Ball
AAA | Cat
AAA | Cat
.
.
.
.
AAA | Zebra
AAA | Zebra
Requirement:
Column1 | Column 2
AAA | Apple
AAA | Ball
AAA | Cat
.
.
AAA | Zebra
AAA | Apple
AAA | Ball
AAA | Cat
.
.
AAA | Zebra
Is this is clear?
March 21, 2011 at 3:23 am
I dont have access to SQL Server at the moment, so consider this pseudo code. It is untested.
create table #Temp
(
GroupID int;
Col1 varchar2(25),
Col2 varchar2(25)
);
Declare
@Group int,
@Col1 varchar2(20),
@Col2 varchar2(20),
@Col1_Prev varchar2(20),
@Col2_Prev varchar2(20)
DECLARE db_cursor CURSOR FOR
SELECT Col1, Col1
FROM theTable
order by Col1, Col2;
set @Group = 1;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Col1, @Col2
Set @Col1_Prev = @Col1;
Set @Col2_Prev = @Col2;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #Temp values(@Group,@Col1,@Col2);
IF @Col1 != @Col1_Prev AND @Col2 != @Col2_Prev THEN
Set @Group = 1;
ELSE
Set @Group = @Group + 1;
END IF;
Set @Col1_Prev = @Col1;
Set @Col2_Prev = @Col2;
FETCH NEXT FROM db_cursor INTO @Col1, @Col2
END
CLOSE db_cursor
DEALLOCATE db_cursor
Select Col1, Col2 From #Temp Order By GroupID,Col1,Col2
March 21, 2011 at 8:00 am
That is a really strange requirement. So basically you have two copies of every row???
what about something like
select distinct col1, col2, 1 as SortOrder
union
select distinct col1, col2, 2
order by SortOrder, col1, col2
If that doesn't get you what you need we are going to need some more info. ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply