tippingpoint
asked on
SQL: merge fields into column dependant on grouping in another field and type
Merging fields into an empty colum dpending on a grouping number and parent/child type.
The best way to explain it is through the table below where the final outcome is what appears in the spare colum.
I can do this over about 6 or 7 different sql commands but when I seem to do it in one go it fails. Can anyone help?
The structure of spare is [fname (of parent in that group) & ' ' & lname (of parent in that group) 'group : ' & fname & lname]
The table has about 50,000 records so needs to be efficient.
table structure:
fname lname company group type spare
aaa zzz 111 0001 parent aaa zzz group
bbb yyy 222 0001 child aaa zzz group : bbb yyy
ccc xxx 333 0001 child aaa zzz group : ccc xxx
ddd www 444 0002 parent ddd www group
eee vvv 555 0002 child ddd www group : eee vvv
fff uuu 666 0002 child ddd www group : fff uuu
The best way to explain it is through the table below where the final outcome is what appears in the spare colum.
I can do this over about 6 or 7 different sql commands but when I seem to do it in one go it fails. Can anyone help?
The structure of spare is [fname (of parent in that group) & ' ' & lname (of parent in that group) 'group : ' & fname & lname]
The table has about 50,000 records so needs to be efficient.
table structure:
fname lname company group type spare
aaa zzz 111 0001 parent aaa zzz group
bbb yyy 222 0001 child aaa zzz group : bbb yyy
ccc xxx 333 0001 child aaa zzz group : ccc xxx
ddd www 444 0002 parent ddd www group
eee vvv 555 0002 child ddd www group : eee vvv
fff uuu 666 0002 child ddd www group : fff uuu
ASKER
I'm still doing testing on it but it seems ok so far...
Will it work for this? The first child record will be that of the parent
fname lname group type spare
aaa zzz 0001 parent aaa zzz group
aaa zzz 0001 child aaa zzz group : aaa zzz
bbb yyy 0001 child aaa zzz group : bbb yyy
ccc xxx 0001 child aaa zzz group : ccc xxx
ddd www 0002 parent ddd www group
ddd www 0002 child ddd www group : ddd www
eee vvv 0002 child ddd www group : eee vvv
fff uuu 0002 child ddd www group : fff uuu
Will it work for this? The first child record will be that of the parent
fname lname group type spare
aaa zzz 0001 parent aaa zzz group
aaa zzz 0001 child aaa zzz group : aaa zzz
bbb yyy 0001 child aaa zzz group : bbb yyy
ccc xxx 0001 child aaa zzz group : ccc xxx
ddd www 0002 parent ddd www group
ddd www 0002 child ddd www group : ddd www
eee vvv 0002 child ddd www group : eee vvv
fff uuu 0002 child ddd www group : fff uuu
ASKER
It is working now as a query but I need to update my 'spare' field in my existing table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff,
COuld you look at my follow up. Its 500 as well:
https://www.experts-exchange.com/questions/21232650/Optimize-my-Union-Select-SQL-statement-500pts.html
thanks.
COuld you look at my follow up. Its 500 as well:
https://www.experts-exchange.com/questions/21232650/Optimize-my-Union-Select-SQL-statement-500pts.html
thanks.
SELECT Table1.*, fname & ' ' & lname & ' group' AS newspare
FROM Table1
WHERE type = 'parent'
UNION SELECT t1a.*, t1b.fname & ' ' & t1b.lname & ' group : ' & t1a.fname & ' ' & t1a.lname AS spare
FROM Table1 t1a INNER JOIN Table1 t1b ON t1a.group = t1b.group
WHERE t1a.type = 'child' AND t1b.type = 'parent'
Obviously, you'll need to change Table1 to the actual name of your table. Let me know if that works for you.
Jeff