I have three columns which I would like to have return as one column BUT have each column add its collection result to the end of the previous one.
Example
Col1 col2 col3
Happy Fun Land
Wally World Here
Result set would be as below
ColAlis
Happy
Wally
Fun
World
Land
Here
I can do this need at a code level on my application but i would rather the SQL statement do it.
Thanks
Here it is
Code Snippet
Create Table #data (
[Col1] Varchar(100) ,
[col2] Varchar(100) ,
[col3] Varchar(100)
);
Insert Into #data Values('Happy','Fun','Land');
Insert Into #data Values('Wally','World','Here');
For SQL Server 2000
Code Snippet
Select Col1 as [Values] From #Data
Union All
Select Col2 From #Data
Union All
Select Col3 From #Data
For SQL Server 2005
Code Snippet
Select [Values] from #Data
Unpivot
(
[Values] for Cols in (Col1,Col2,Col3)
) as Upvt Order By Cols
|||
Code Snippet
create table #t (Col1 varchar(10), col2 varchar(10), col3 varchar(10))
insert into #t
select 'Happy', 'Fun', 'Land'
union all select 'Wally', 'World', 'Here'
select Items
from
(select * from #t) p
unpivot
( items for data in (col1, col2, col3) ) as x
OR
Code Snippet
select col1 from #t
union all select col2 from #t
union all select col3 from #t
|||Try this:SELECT col1+' '+col2+' '+col3 FROM yourtable
Have a nice day!
Edited:
I'm sorry, didn't read the desired result hehe.
This is not an option...|||
Try:
Code Snippet
declare @.t table (
col1 varchar(25),
col2 varchar(25),
col3 varchar(25)
)
insert into @.t values('Happy', 'Fun', 'Land')
insert into @.t values('Wally', 'World', 'Here')
select
t2.c1 as col,
case
when t2.c1 = 1 then col1
when t2.c1 = 2 then col2
when t2.c1 = 3 then col3
end as [value]
from
@.t as t1
cross join
(select 1 as c1 union all select 2 union all select 3) as t2
order by
col,
[value]
-- 2005
select
col,
[value]
from
(select col1 as [1], col2 as [2], col3 as [3] from @.t) as p
unpivot
([value] for col in ([1], [2], [3])) as unpvt
order by
col,
[value]
go
AMB
|||Excellent suggestions Mani, Dale, Alejandro!
However, both solutions 'almost' produce the same resultset -not quite what the OP asked.
While two solutions (SQL 2000 / SQL 2005) was not specifically requested, it seems that if two solutions are provided, that they should both provide the same resultset to address the OP's problem. It's just a matter of quality control.
It seems that the 'closest' solution may perhaps be the simplest solution -adding an artificial grouping indicator. Since with the OP's data, there is no way to precisely determine the order of a column, either we have to conclude that we cannot derive an accurate solution for the problem, or we take corrective actions.
Here is a suggestion that 'should' alleviate the ordering problem:
Code Snippet
DECLARE @.Data table
( Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);
Insert Into @.Data Values ( 'Happy', 'Fun', 'Land' );
Insert Into @.Data Values ( 'Wally', 'World', 'Here' );
DECLARE @.Data2 table
( RowOrder int IDENTITY,
Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);
INSERT INTO @.Data2
SELECT *
FROM @.Data;
--Works in both SQL 2000/2005
SELECT
1 AS Collection,
RowOrder,
Col1 AS [Values]
FROM @.Data2
Union All
SELECT
2 AS Collection,
RowOrder,
Col2 AS [Values]
FROM @.Data2
Union All
SELECT
3 AS Collection,
RowOrder,
Col3 AS [Values]
FROM @.Data2
ORDER BY
Collection,
RowOrder
Collection RowOrder Values
-- -- --
1 1 Happy
1 2 Wally
2 1 Fun
2 2 World
3 1 Land
3 2 Here
Of course, there 'could' be a slight reorganization of the data order in the process of inserting from the original table into the 'identified' table (@.Data2) -but under most circumstances, that should be minimal.
(Dale, Alejandro -please send me an email -Arnie)
|||Hi Arnie,
I am using "order by" clause to be consistent, but the result is lightly different from what the OP expected (values from col3 - 'Here', 'Land'). There is not an easy way to use a row number, to know from which row is every value coming from.
Thanks,
Alejandro Mesa
|||You're absolutely correct Arnie.
Thanks!
I think I'm in need of a vacation 
Hi Arnie,
I agree with you, but I wonder how are you assuring that the data will be inserted into the second table in the same order you are inserting it into the first one.
> INSERT INTO @.Data2
> SELECT * FROM @.Data;
this statement does not guarantee that the row ( 'Happy', 'Fun', 'Land' ) will be the first one. Just add a clustered index (we need a table and not a table variable in order to do this) to table [Data], by [col1] DESC and that solution will fail. Check also the attached link.
Code Snippet
create table dbo.Data
( Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);
Insert Into dbo.Data Values ( 'Happy', 'Fun', 'Land' );
Insert Into dbo.Data Values ( 'Wally', 'World', 'Here' );
go
create clustered index Data_col1_nu_c_ix
on dbo.Data(col1 DESC)
go
create table dbo.Data2
( RowOrder int IDENTITY,
Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);
INSERT INTO dbo.Data2
SELECT *
FROM dbo.Data;
--Works in both SQL 2000/2005
SELECT
1 AS Collection,
RowOrder,
Col1 AS [Values]
FROM dbo.Data2
Union All
SELECT
2 AS Collection,
RowOrder,
Col2 AS [Values]
FROM dbo.Data2
Union All
SELECT
3 AS Collection,
RowOrder,
Col3 AS [Values]
FROM dbo.Data2
ORDER BY
Collection,
RowOrder
go
drop table dbo.Data, dbo.Data2
go
The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause
http://support.microsoft.com/kb/273586
AMB
|||Alejandro,
I agree totally, since the OP didn't provide any indication of sequecing, positioning is totally happenstance.
Without a definitive sequencing indicator on the primary table, and without the option to alter the primary table to add such (PK, IDENTITY, Index, etc.), there is little that can be done to absolutely ensure sequence. The only option is to temporarily assert sequencing -as my example of moving into a temp table with IDENTITY. Whether or not it the sequence matches the primary table is totally by accident -BUT sequence can then be maintained for additional activities using the temp table. It just may be a different sequence the 'next time'.
|||I haven’t dug deep into these solutions so I apologize in advance if I ask for clarification of what it does vs. what I need.
What I don’t want
Record 1 = 'HappyFunLand'
Record 2 = 'WallyWorldHere'
I want one column returned that says
Happy
Wally
Fun
World
Land
Here
The order I have listed above if look closely is actually contents of col1 then append contents of col2 then append contents of col3 all returned as one column.
So in the example below
insert into @.t values('Happy', 'Fun', 'Land')
insert into @.t values('Wally', 'World', 'Here')
I would be inserting only the first two rows of the original table from my understanding
Here is a more specific example of the real table
Column 1 = EnduserEmail
Column 2 = TechEmail
Column3 = managerEmail
I want to populate one dropdown with all emails from all columns without doing any coding which I could do but I would rather keep it on SQL if I can.
If your SQL statement or someone else’s does this I thank you all greatly, just point me to the correct post number.
Reason I am clarifying myself is only because I don’t want to learn the statement only to find it doesn’t do what I am looking for.
Thanks again!
|||Sorry everyone I think I figured it out.
Example below
select Email1 from myTable
union all
Select Email2 from myTable
union all
Select Email3 from myTable
Orderby would simply be alpha numeric in my case which i assume is standard orderby.
Sorry I completely forgot about UNION statement
|||Sean,
There is NO 'standard' sorting or ordering UNLESS you specifically use an ORDER BY.
Without the ORDER BY, it is just by chance.
|||Right, what I ment to say was as long as a standard ORDER BY statement would work the same in the context of UNION statement as it would with a simple SELECT statement.
And it did perfectly.
Thanks again I have my sproc now.
No comments:
Post a Comment