query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
===== id name
1 Arun
2 Hari
Table2
===== id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queries

Query to get a list of field/column names with the tablm name.

Thanks for reading my question.

I need some help on a query which will provide me with the following information.

TableName ColumName

Table1 Column1

Table1 Column2

Table1 Column3

Table2 Column1

Table2 Column2

Table2 Column3

Table2 Column1

Table3 Column1

Table3 Column2

Thanks.

I got it...

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS

ColumnName, dbo.systypes.name AS DataType, dbo.syscolumns.length,

dbo.syscolumns.xprec, dbo.syscolumns.xscale,

dbo.syscolumns.colid AS ColumnSort, dbo.sysindexkeys.colid

FROM dbo.sysobjects INNER JOIN

dbo.syscolumns ON dbo.sysobjects.id =

dbo.syscolumns.id INNER JOIN

dbo.systypes ON dbo.syscolumns.xusertype =

dbo.systypes.xusertype LEFT OUTER JOIN

dbo.sysindexkeys ON dbo.syscolumns.colid =

dbo.sysindexkeys.colid AND dbo.syscolumns.id = dbo.sysindexkeys.id

WHERE (dbo.sysobjects.xtype = 'U')

|||

Thanks for following up your own post. It prevents others from wasting their time trying to help you after you have solved the problem, and it helps others when you share your solution.

|||

use information_schema.columns

Code Snippet

select table_name,column_name from information_schema.columns

order by table_name,ordinal_position

|||

select Table_catalog as Databasename, table_name, column_name from INFORMATION_SCHEMA.COLUMNS

|||

DON'T write your query against the SYSTEM tables, the schema might change without any notice. Always trust with information_schema.xxxx views.

I do like to play with system tables, but when it come to solid solutions, we have to stick with few coditions. |||

Sorry for that unnecessary answer from me. Arnie/Mani and Team are really quick to answer any question. When i open this thread nobody was answered. But when i completed typing there were two reply... Really quick buddies...

g8 keep it up

Madhu

|||

Madhu,

Sometimes that happens this time of day. For slower responses, and a longer 'window of opportunity, try about four hours later in the day.

Query syntax for count comparison

I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
It would be easier for us to understand your requirement, if you posted some
sample data to work with, and the expected resultset.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ysandre" <ysandre@.hotmail.com> wrote in message
news:2DFCF9B8-D4C6-4330-932A-678B0687FC86@.microsoft.com...
I have 2 tables, with 2 columns each: Seq# and Client.
I need the syntax of a query to compare count(Client) by Seq#, between
Table1 and Table2.
Something like this (although this doesn't work):
Select Table1.Seq#
from Table1 inner join Table2 on Table1.Seq# = Table2.Seq#
where count (table1.Client) = count (table2.Client)
Is this possible? If so, please provide correct syntax.
Thank you!
|||Take a look at FULL OUTER JOIN in SQL Server Books Online. In simple cases
similar to yours, one can use it to compare data across different tables.
Anith