I need to output a single row of multiple columns to multiple rows

[Randy]

so I have a table
userid,group1,group2,group3....group18

anyone user would have a group1 value or a group1 and group2 etc.
so
ether,admin,security,query
smith,admin,query,tax,entry

I need to output this into this format
ether,admin
ether,security
ether,query
smith,admin
smith,query
smith,tax
smith,entry

I had this same issue a long time ago, but cannot find the database to see the solution.

Any help is appreciated. thanks

 

[Mark_]

First thing I'd do is ask "Why do you have these groups as fields in a record rather than child records?"

Second, to answer your immediate question, I'd create 18 queries. Each would have UserID and the matching group. This means Query 1 would be UserID, Group1. Query 2 would be UserID, Group2. At the end I'd just union them all together to get the results you are looking for.

 

[Randy]

I have no answer for your first question, the information is provided to me by a third party source. Eventually I will get around to discussing the layout with them, but I have this immediate need.

second I did not think about 18 queries, and a union. I guess it is brute force but it would work. thanks.

 

[Mark_]

Is this being sent as a spreadsheet by chance? If so, I'd import to a temporary table, then use the "multiple queries" approach to create normalized data in your database.

 

[Randy]

yes an excel file. for now the union query worked just fine.

 

[June7]

The UNION would have 18 SELECT lines but don't see need for 18 SELECT query objects.

SELECT UserID, 1 AS SrcGrp, Group1 AS Data FROM table
UNION SELECT UserID, 2, Group2 FROM table
…
UNION SELECT UserID, 18, Group18 FROM table;