help combing multiple columns into one in sql

[evansc]

I'm going crazy!! Someone who didn't really understand relational database theory (not like I'm an expert, but. . .) built a DB that is much more complicated than it needs to be. That person left and I got the job. I have to work with our IT people to hopefully redesign it completely (I would without asking, but they control the SQL server and the connection to the web.) but in the meantime, I have to work with a mess.

Okay, the question--I have to print a report of which organizations used which software company.

The table fields are:

Court
DBSoftware1
DBSoftware2
DBSoftware3
DBSoftware4
etc.

the answers are a "1" for a yes and a "0" for no in the "Software" fields, so there is an answer in every field of a record. Essentially, it's a spreadsheet.

Off the top of my head, I knew I could query each court name with each software column individually and then union query them all together. But I thought there must be an easier way.

So I did the following query (sorry--it's huge!!) It puts in the field the name of the DB if it there is a 1, and if not it looks through each of the following fields until it finds a one and puts the appropriate name. But with just that, if court used more than one db, the second one wouldn't show. So the following sql adds a new column and does the same thing from the second software on, and then the third does the same, and so on.

The problem is, this means that for those that use only one DB, no problem--it's all in the first column. But for those that use multiple, I still have to somehow combine all that information into one column. Hmmm--maybe union queries would have been easier just because I can do them!!

It would be so much better if the info would appear:
court vendor
court vendor
court vendor (etc.)

Anyway, thanks for any help you can give--this is driving me crazy (over 40 table set up in similar disarray. . . .)


SELECT QRY_Q7_DatabaseProducts.Court_Name_VC120, IIf([SQL]=1,"SQL",(IIf([FilePro]=1,"FilePro",(IIf([Sybase]=1,"Sybase",(IIf([Progress]=1,"Progress",(IIf([Oracle]=1,"Oracle",(IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access","Do Not Know"))))))))))))) AS DatabaseProduct, IIf([FilePro]=1,"FilePro",(IIf([Sybase]=1,"Sybase",(IIf([Progress]=1,"Progress",(IIf([Oracle]=1,"Oracle",(IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access",""))))))))))) AS DatabaseProduct2, IIf([Sybase]=1,"Sybase",(IIf([Progress]=1,"Progress",(IIf([Oracle]=1,"Oracle",(IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access",""))))))))) AS DatabaseProduct3, IIf([Progress]=1,"Progress",(IIf([Oracle]=1,"Oracle",(IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access",""))))))) AS DatabaseProduct4, IIf([Oracle]=1,"Oracle",(IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access",""))))) AS DatabaseProduct5, IIf([Raining Data]=1,"Raining Data",(IIf([Access]=1,"Access",""))) AS DatabaseProduct6, IIf([Access]=1,"Access","") AS DatabaseProduct7
FROM QRY_Q7_DatabaseProducts;

 

[PHV]

Have you tried this ?
SELECT Court_Name_VC120, IIf([SQL]=1,"SQL ","") & IIf([FilePro]=1,"FilePro ","") & IIf([Sybase]=1,"Sybase ","") & IIf([Progress]=1,"Progress ","") & IIf([Oracle]=1,"Oracle ","") & IIf([Raining Data]=1,"Raining Data ","") & IIf([Access]=1,"Access ","") AS DatabaseProduct
FROM QRY_Q7_DatabaseProducts;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[evansc]

Oh my goodness--now I guess I'M making things complicated. Thanks so much!!