Issues with Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Issues with Column

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-01 : 12:29:59

I have table A

Field A FieldB Field C
a 2 3
b 3 4

Other table has value for a

TableB

A(col)
1
2
3
4

I need to get this final output:
Field A FieldB FieldC
1 2 3
2 2 3
3 2 3
4 2 3
......... so on for b

raky
Aged Yak Warrior

767 Posts

Posted - 2009-05-01 : 12:41:27

try this

declare @table1 table (FieldA char(1),FieldB int, FieldC int )
insert into @table1
select 'a', 2, 3 union all
select 'b', 3, 4

--select * from @table1

declare @table2 table (a int )

insert into @table2
select 1 union all
select 2 union all
select 3 union all
select 4

select a as fielda,fieldb,fieldc from @table1,@table2 order by fielda

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-01 : 12:45:40

quote:
Originally posted by raky

try this

declare @table1 table (FieldA char(1),FieldB int, FieldC int )
insert into @table1
select 'a', 2, 3 union all
select 'b', 3, 4

--select * from @table1

declare @table2 table (a int )

insert into @table2
select 1 union all
select 2 union all
select 3 union all
select 4

select a as fielda,fieldb,fieldc from @table1,@table2 order by fielda

No this doesn't work.

I know we have to use PIVOT or crosstab but don't know how to.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 13:04:41

quote:
Originally posted by SCHEMA
No this doesn't work.

I know we have to use PIVOT or crosstab but don't know how to.

Why? Are you asking for a solution using PIVOT or someother method than Raky's?

If you change Raky's query you'll get the proper order:
select a as fielda,fieldb,fieldc from @table1,@table2 order by fieldb, fielda

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-01 : 13:11:25

I have other values for 'b'

5
6
7
8

So for b
output:
5 3 4
6 3 4
7 3 4
8 3 4

So it needs to loop according to FieldA in TableA
a,b,c.....

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-01 : 14:57:54

can you prove sample data and expected output so we can have some data to work with? I'm not clear on where 'b' is located.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2009-05-01 : 15:03:37