SQL Server Performance Forum – Threads Archive

how can i display this?

Hello everybody,
I need some help in display some information from two temporal tables.
In first table i have this:
Table 1(idl, companyorg, article, firstprice)
Table 2(idli, companyparticipate, last price, datatime1)
the result i want to look like this:
companyorg article fistprice
companyparticipate lastprice datatime1
companyorg article fistprice
companyparticipate lastprice datatime1
companyparticipate lastprice datatime1 it is posilbe that the companyorg which organised an auction can have 2 companies or more who participated at this. so I want under evry companyorg to dispaly all companies participated. I want to do this without cursor. i made this
select t1.companyorg, t1.article, t1.firstprice, t2.companyparticipate, t2.lastprice, t2.datatime1
from table1 t1 join table2 t2 on t1.idl=t2.idli but if it isn’t what i want to display. If you have any ideea please help me. Thanks you!
Ana

Where do you want to show these data like this? Are you using any Front End Application? If so you can do this there [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

True that helps to give you right answer.
On SQL side if you want to relate the tables you can define the relationship using your criteria. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I don’t use any front-end application. I just need to return those dates. Do you have an ideea? Thanks!

Can you post table structure, sample data and expected output?
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

table1 ( ID int identity(1,1), idl int, companyorg varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, article varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, firstprice varchar(30), quantity varchar(20)) table2 (id int identity(1,1), idlic int, companyparticipated varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, lastprice numeric(20,2), datatime1 datetime) i want to get this if one of companyorg has many companies participated companyorg article firstprice quantity companyparticipated lastprice datatime1 companyorg article firstprice quantity companyparticipated lastprice datatime1 companyparticipated lastprice datatime1 table 1 (1 2 KLM paper 2 50 2 4 DLS pen 0.2 100) table 2 (1 2 BIT 1.8 2005-02-02 2 2 AERO 1.7 2005-02-05 3 4 TIB 0.17 2005-03-05) the result which i want is this: KLM paper 2 50 BIT 1.8 2005-02-02 AERO 1.7 2005-02-05 DLS pen 0.2 100 TIB 0.17 2005-03-05 i don’t want to use cursor. thanks

It is difficult to do without using Cusrsor
Why do you want to show data like this?
What are you going to do with that result? Madhivanan Failing to plan is Planning to fail

I want to show data like this for a report.
do you have an ideea how can I do this without cursor? Ana

The only way you can do is Create a new table having same columns and insert them to that table Declare @t1 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
insert into @t1 values(1,’Test1′,’Test2′,’Test3′)
insert into @t1 values(2,’Test2′,’Test3′,’Test4′) Declare @t2 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
insert into @t2 values(1,’Test11′,’Test12′,’Test13′)
insert into @t2 values(2,’Test12′,’Test13′,’Test14′)
insert into @t2 values(2,’Test12′,’Test13′,’Test14′) Declare @t3 table(id int,data1 varchar(100),data2 varchar(100), data3 varchar(100))
Insert into @t3 Select * from @t1
Insert into @t3 Select * from @t2
Select * from @t3 order by 1
Madhivanan Failing to plan is Planning to fail

Hy,
Thanks for your help. I made some change at your advise but it worked. I received
KLM paper 2 50
BIT 1.8 2005-02-02
AERO 1.7 2005-02-05
DLS pen 0.2 100
TIB 0.17 2005-03-05 My guestion is how can I obtine this if one of the company have two auctions or more? KLM paper 2 50
BIT 1.8 2005-02-02
AERO 1.7 2005-02-05
DLS pen 0.2 100
TIB 0.17 2005-03-05
DLS pencilbox 4 40
GH 3 2005-04-06
HY 3.5 2005-04-05 and I want this
KLM
paper 2 50
BIT 1.8 2005-02-02
AERO 1.7 2005-02-05
DLS
pen 0.2 100
TIB 0.17 2005-03-05 pencilbox 4 40
GH 3 2005-04-06
HY 3.5 2005-04-05 thanks for help Madhivanan

Post the query you used Madhivanan Failing to plan is Planning to fail

declare @t4 table(idl int, company varchar(100),article varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,firstprice varchar(100), quantity varchar(100)) insert into @t4 select l.id, u.companyorg , ”,”,” from lic l join article p on l.idp = p.id join uti u on u.id=l.idorg group by u.companyorg,l.id declare @t2 table(idl int, company varchar(100),article varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,firstprice varchar(100), quantity varchar(100)) insert into @t2 select l.id,”, p.nume,l.firstprice,l.quantity from lic l join article p on l.idp = p.id join uti u on u.id=l.idorg order by p.nume declare @t1 table(idl int, company varchar(100),article varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,firstprice varchar(100), quantity varchar(100)) insert into @t1 select l.id, ”,’org’+ ‘ ‘+cas.company, l.lastprice,convert(varchar(10),l.datai,103) from licx l join uti cas on l.idpa=cas.id where l.idli in (select l.id from lic l join article p on l.idprodus = p.id join uti u on u.id=l.idorg) declare @t3 table(idl int, company varchar(100),article varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,firstprice varchar(100), quantity varchar(100)) Insert into @t3 Select * from @t4 Insert into @t3 Select * from @t1 Insert into @t3 Select * from @t2 Select * from @t3 order by idl ,firstprice asc,quantity desc this i did

Whats wrong here?
Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
insert into @t1 values(1, 2, ‘KLM paper’, 2, 50)
insert into @t1 values(2, 4, ‘DLS pen’, 0.2, 100) Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
insert into @t2 values(1, 2, ‘BIT’ ,1.8, ‘2005-02-02’)
insert into @t2 values(2, 2, ‘AERO’, 1.7 ,’2005-02-05′)
insert into @t2 values(3, 4, ‘TIB’, 0.17, ‘2005-03-05’) Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
Insert into @t3 Select * from @t1
Insert into @t3 Select * from @t2
Select data3,data4,data5 from @t3 order by data1 Madhivanan Failing to plan is Planning to fail

KLM is a field and paper another field from table.

Did you execute the query I have given? Madhivanan Failing to plan is Planning to fail

try this and maybe you will understand what i want
Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
insert into @t1 values(1, 2, ‘KLM paper’, 2, 50)
insert into @t1 values(1, 3, ‘KLM pencil’, 5, 50)
insert into @t1 values(2, 4, ‘DLS pen’, 0.2, 100) Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
insert into @t2 values(1, 2, ‘BIT’ ,1.8, ‘2005-02-02’)
insert into @t2 values(2, 2, ‘AERO’, 1.7 ,’2005-02-05′)
insert into @t2 values(3, 3, ‘TIB’, 4, ‘2005-03-05’)
insert into @t2 values(4, 4, ‘TIB’, 0.17, ‘2005-03-05’) Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 float, data5 varchar(50))
Insert into @t3 Select * from @t1
Insert into @t3 Select * from @t2
Select data3,data4,data5 from @t3 order by data1

What is your expected result from that? Madhivanan Failing to plan is Planning to fail

KLM
paper 2 50
‘BIT’ ,1.8, ‘2005-02-02’
‘AERO’, 1.7 ,’2005-02-05′
pencil’, 5, 50
‘TIB’, 4, ‘2005-03-05’
DLS
pen’, 0.2, 100
‘TIB’, 0.17, ‘2005-03-05’) this i want thanks

Have anybody an ideea ?
Thanks!

Ana, it’s not that we don’t want to help, but, at least I, have a very hard time to understand what you’re trying to accomplish. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

If you want to split the data, here is an example
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=10846 Madhivanan Failing to plan is Planning to fail

Declare @t1 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50), data6 varchar(50))
insert into @t1 values(1, 2, ‘KLM’, ‘paper’, 2, 50)
insert into @t1 values(1, 3, ‘KLM’,’pencil’, 5, 50)
insert into @t1 values(2, 4, ‘DLS’, ‘pen’, 0.2, 100) Declare @t2 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50),data6 varchar(50))
insert into @t2 values(1, 2, ”,’BIT’ ,1.8, ‘2005-02-02’)
insert into @t2 values(2, 2, ”,’AERO’, 1.7 ,’2005-02-05′)
insert into @t2 values(3, 3, ”,’TIB’, 4, ‘2005-03-05’)
insert into @t2 values(4, 4, ”,’TIB’, 0.17, ‘2005-03-05’) Declare @t3 table(data1 int, data2 int, data3 varchar(50),data4 varchar(50), data5 varchar(50),data6 varchar(50))
Insert into @t3 Select * from @t1
Insert into @t3 Select * from @t2
Select data3,data4,data5,data6 from @t3 order by data1 Please rule this. what i want me to show it is that i want to look like this
KLM
paper250
BIT1.82005-02-02
AERO1.72005-02-05
pencil550
TIB42005-03-05
DLS
pen0.2100
TIB0.172005-03-05

Is this some kind of homework? When you want to display this in a report why don’t you use the formatting features of your report writer and insist of finding an, at best, kludgy solution in T-SQL??? Such hierarchical reports should be an easy exercise of any reporting tool. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I have a big database who can return for one company more than an entrance, I can have more that 100 companies. So I do this report in SQL and then I will put it in excell. Can you explain me how can i do this?

Which reporting tool are you using?
Use split function of Reports like Split(column,’ ‘) Madhivanan Failing to plan is Planning to fail

]]>