Query help

CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your helpYep:
select
s.c1
, x.a
from
stuff
cross join
(
select 1 union all
select 2 union all
select 3
) as x (a)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jack" <jack@.jack.net> wrote in message
news:9uaje.13324$4d6.11003@.trndny04...
CREATE TABLE [dbo].[stuff] (
[c1] [char] (10)
)
insert into stuff values ('a')
insert into stuff values ('b')
Is there an easy way to get this output ?
a 1
a 2
a 3
b 1
b 2
b 3
Thank you in advance for your help|||> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare|||Well,
I did write one or two that involved a cross join. Here's one that
un-pivots a table:
create table Budgets
(
Contract int not null
, Nominal int not null
, Budget_01 int null
, Budget_02 int null
, Budget_03 int null
, primary key (Contract, Nominal)
)
go
insert Budgets values (1, 123, 1000, 1000, 2000)
insert Budgets values (1, 234, 500, 500, 1000)
insert Budgets values (2, 456, 3000, 4500, 3000)
insert Budgets values (2, 567, 800, 800, 800)
insert Budgets values (3, 789, 500, 500, 500)
insert Budgets values (3, 987, 5000, 500, NULL)
go
select
*
from
(
select
b.Contract
, b.Nominal
, x.Period
, case x.Period
when 1 then b.Budget_01
when 2 then b.Budget_02
when 3 then b.Budget_03
end as Budget
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3
) as x
) as y
where
Budget is not null
order by
Contract
, Nominal
, Period
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23l4YUHOXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> select
> s.c1
> , x.a
> from
> stuff
> cross join
> (
> select 1 union all
> select 2 union all
> select 3
> ) as x (a)
Wow, that's really badass. I as the VB.NET guy would have wasted precious
seconds slurping it into a DataTable object and then doing a whole bunch of
monkey business.
RESPECT!
This is the stuff that articles are made of!!
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare