SQL Help

[Sarnie83]

Hi

I previously added this question to the end of another thread but think it might needs it's own thread.

What I am looking to do is pull all of the serial numbers into one column, against the UniqueIdentifier that relates to that serial number.

This is the data I am using.

SELECT SM_Cabinet_T.UNIQUEIDENTIFIER, SM_Cabinet_T.A1TX1RECEIVER, SM_Cabinet_T.A1TX1DUPLEXERASSEMBLY, SM_Cabinet_T.A1TX1COMBINER, SM_Cabinet_T.A1TX1TRANSMITTERCHASSIS, SM_Cabinet_T.A1TX1POWERAMPLIFIER1, SM_Cabinet_T.A1TX1POWERAMPLIFIER2, SM_Cabinet_T.A1TX1POWERAMPLIFIER3, SM_Cabinet_T.A1TX1POWERAMPLIFIER4
FROM SM_Cabinet_T;

All of the fields above contain serial numbers that relate to a number in the UniqueIdentifier column.
What I'd also like to know if you can do on a SQL statement, is add a field in the query that shows which column the serial number originally came from. I'm not sure if this is something you can actually do though......

So essentially I would like the end query to have 3 columns. The UniqueIdentifier, Serial Numbers, and a column that gives the fields that the serial numbers came from.......this is bascially a descpription of the product.

Thanks in advance

 

[jdraw]

It seems, based on your post, that you have several fields that are related. It would be helpful to readers if you could describe what the database is about in plain English. It would also be helpful if you could give an example of some records showing how the numbers are related.

 

[namliam]

Ingore the obvious concerns for your database structure, perhaps you mean something like this?

SELECT SM_Cabinet_T.UNIQUEIDENTIFIER
     , SM_Cabinet_T.A1TX1RECEIVER
     , "A1TX1RECEIVER" as fieldname
FROM SM_Cabinet_T
Union all
SELECT SM_Cabinet_T.UNIQUEIDENTIFIER
     , SM_Cabinet_T.A1TX1DUPLEXERASSEMBLY
     , "A1TX1DUPLEXERASSEMBLY" as Fieldname
FROM SM_Cabinet_T
Union all
SELECT SM_Cabinet_T.UNIQUEIDENTIFIER
     , SM_Cabinet_T.A1TX1COMBINER
... etc ...

 

[Sarnie83]

The database is something I have inherited from elsewhere, that before has just been used for storage of serial numbers. I am looking to pull this data out into something I can submit to our operations team once the "kit" is handed over.

There are several components that make up the kit, each of which has a different serial number.....so in this case te components are an A1TX1DUPLEXERASSEMBLY and A1TX1COMBINER etc.

There are a numbe of these components, and each one is related to a UNIQUEIDENTIFIER number, so once that kit has been sent out to a specific location, I am reported back to on the unique identifier number.

The current spreadsheet has the kit all on one line against this unique identifier number with the serial numbers all in a long row.

What I am looking to do is put this into a different format so that it reads as per the attachment for example.

Does that make more sense.......and is it something that can be done?

 

[namliam]

did you see my previous post?

 

[Sarnie83]

Ooops - just seen that and yess it works! I have added in an ORDER BY 1 at the end and its come back with the results I wanted!

Thanks