I need to output a single row of multiple columns to multiple rows (1 Viewer)

R

Randy

Registered User.

Local time

Today, 00:47

Joined

Aug 2, 2002

Messages

94

• May 15, 2019

• #1

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

 

M

Mark_

Longboard on the internet

Local time

Yesterday, 21:47

Joined

Sep 12, 2017

Messages

2,111

• May 15, 2019

• #2

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.

 

R

Randy

Registered User.

Local time

Today, 00:47

Joined

Aug 2, 2002

Messages

94

• May 15, 2019

• #3

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.

 

M

Mark_

Longboard on the internet

Local time

Yesterday, 21:47

Joined

Sep 12, 2017

Messages

2,111

• May 15, 2019

• #4

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.

 

R

Randy

Registered User.

Local time

Today, 00:47

Joined

Aug 2, 2002

Messages

94

• May 15, 2019

• #5

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

 

June7

AWF VIP

Local time

Yesterday, 20:47

Joined

Mar 9, 2014

Messages

5,463

• May 15, 2019

• #6

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;