[sql]concatenate data from diff tables in one row?

DD · 03-04-2008 08:45PM #1

I have a big select but I need to make 3 cursors or 3 functions to concatenate data from different tables into one row, without making temporary tables so I can have the result of my select. I was supposed to get it done this week...if someone can help me, pls tks

my sql script returns smth like this;

contract1, a, b, c, d, aaaaa, doc1
contract1, a, b, c, d, bbbbb, doc1
contract1, a, b, c, d, ccccc, doc2
contract1, a, b, c, d, ddddd, doc2

and i need in the end to get the result in one line
contract1, a, b, c, d, aaaaa | bbbbb | ccccc | ddddd , doc1 | doc2
aaaaa | bbbbb is aaaaa concatenated with bbbbb in one single row, but the only connection I can make with them is the contract which is in another table

Tom Dunne · 03-04-2008 08:51PM

dianaangeleyes wrote: »

I have a big select but I need to make 3 cursors or 3 functions to concatenate data from different tables in one row, without making temporary tables so I can have the result of my select. I was suppose to get it done this week...if someone can help me, pls pm me. tks

You really should read the forum charter.

Post up what you already have and we will see if we can help you.

kayos · 04-04-2008 09:18AM

The rules of SQL query writing

1) Cursors are bad
SQL is set based and if you are running through the set using a cursor there is performance implications. In my time working with SQL I've only seen about 5% of cursors that could not be rewritten to use a, easier and faster, set based approach.
2) Using DISTINCT is bad
In nearly all cases if you need to use distinct either your query is poorly written or you should smack the person who designed the schema.
3) See 1
4) See 2

As for your problem its homework and even if it wasnt you really did not give a lot of info for us to help you with. If I read your post right you seem to miss the fact you can join tables together in a query....

tiptap · 04-04-2008 09:27AM

kayos wrote: »

The rules of SQL query writing
1) Cursors are bad
SQL is set based and if you are running through the set using a cursor there is performance implications. In my time working with SQL I've only seen about 5% of cursors that could not be rewritten to use a, easier and faster, set based approach.

I wouldn't entirely agree with this !
I use cursors where I need to execute stored procs per row. Far better performance that using a loop.

But, of course, in the OPs example, a cursor is definetely not needed, given the information he's given us.

Tom Dunne · 04-04-2008 11:46AM

Google "rows to columns".

I recall having to do something similar a while back and found a nifty Oracle procedure to do it.

What database are you using.

kayos · 04-04-2008 11:54AM

tiptap wrote: »

I wouldn't entirely agree with this !
I use cursors where I need to execute stored procs per row. Far better performance that using a loop.

But, of course, in the OPs example, a cursor is definetely not needed, given the information he's given us.

You will notice I said there are some that need cursors. I suppose where I am coming from is a world where poor programmers where let run wild in writing SQL and it nearly always ended up in a massive unreadable mess of nested cursors and stored procs as they try to do things their normal row based way.

I've written plenty of cursors in my time but they have been mainly where there is no other choice or like you gave an example of I'm writing/running code based on each row.

Its like this a lot of programmers will code a cursor into a Stored Procedure and forget that while the performance is totally acceptable on a small row set that you would have on a early dev DB, when that row set grows in a live environment the performance becomes a much bigger factor.

tiptap · 04-04-2008 11:59AM

kayos wrote: »

You will notice I said there are some that need cursors. I suppose where I am coming from is a world where poor programmers where let run wild in writing SQL and it nearly always ended up in a massive unreadable mess of nested cursors and stored procs as they try to do things their normal row based way.

I've written plenty of cursors in my time but they have been mainly where there is no other choice or like you gave an example of I'm writing/running code based on each row.

Its like this a lot of programmers will code a cursor into a Stored Procedure and forget that while the performance is totally acceptable on a small row set that you would have on a early dev DB, when that row set grows in a live environment the performance becomes a much bigger factor.

I agree with you.

Most awful coding though I've noticed is just down to pure bad database design unfortunately, which should be the easy part !

DD · 04-04-2008 12:43PM

kayos wrote: »

The rules of SQL query writing

As for your problem its homework and even if it wasnt you really did not give a lot of info for us to help you with. If I read your post right you seem to miss the fact you can join tables together in a query....

Can't do it only with selects, I gather the data from lot of tables and I get for every contract different values of ..let's say services that I need to group them in one single row and the diff values concatenated in one single field..
and im not allowed to create a temp table

amen · 04-04-2008 01:45PM

post some sample data and a sample schema

kayos · 04-04-2008 02:22PM

Temp tables can easily be replaced by something like Derived tables, Inline table functions, table variables, or maybe a Common Table Expression, subqueries, heck even views could help you out! But honestly from the scant description you have given and the total lack of any sort of schema its very hard for any one to help you. Given the fact that some of the ways of avoiding temp tables I've listed above may or may not be supported on your RDBMS.

What RDBMS and Version are you running on?
Priovide the table schema
Give some sample data for each table
Provide the results you looking for from that sample data

@amen yup we've all seen them and are most likely both thinking of the same db :P.

stevenmu · 04-04-2008 02:31PM

Look into aliases. They will let you select bits of different rows from the same table. For e.g. if you had a table called table1 you could have

SELECT table1.field1, table1alias.field2
FROM table1, table1 as table1alias
WHERE table1.IDField = x AND table1alias.IDField = y

[sql]concatenate data from diff tables in one row?

Comments