CODEWITHSUNDEEP
sqlms-access-2016
Kyle Quigley
Kyle Quigley

Reputation: 35

Transpose one row of a table access

I have a table where each row has a Scale Name and then 6 points representing the 6 Reponses to a survey

Scale Point1 Point2 Point3 Point4 Point5 Point6
Ability Extremely Unable Moderately Unable Somewhat Unable Somewhat Able Moderately Able Extremely Able

I need to figure out how to write a query that will return:

ID Response
1 Extremely Unable
2 Moderately Unable
3 Somewhat Unable
4 Somewhat Able
5 Moderately Able
6 Extremely Able

I've tried to do this with the crosstab query wizard and SQL but I'm not making any headway.

UPDATE - This is what I ended up with:

SELECT 1 AS ID, [Ref-SCALELIST].Point1 AS Response
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 2 , [Ref-SCALELIST].Point2
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 3, [Ref-SCALELIST].Point3
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 4, [Ref-SCALELIST].Point4
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 5, [Ref-SCALELIST].Point5
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
UNION
SELECT 6, [Ref-SCALELIST].Point6
FROM [Ref-SCALELIST]
WHERE ((([Ref-SCALELIST].SCALE)="Ability"))
;

"Ability" is going to be replaced with a variable so I can dynamically update a combo box based on which scale is selected. Convoluted, but at least I avoid creating a table or query for each scale in my list (>30)

Upvotes: 0

Views: 37

Answers (1)

Jocohan
Jocohan

Reputation: 384

You could try something like this:

WITH col as(
  select '1' AS c
  UNION ALL
  select '2' AS c
  UNION ALL
  select '3' AS c
  UNION ALL
  select '4' AS c
  UNION ALL
  select '5' AS c
  UNION ALL
  select '6' AS c
  )
  select
  c 'ID',
  CASE c
    when '1' then "Extremely Unable"
    when '2' then "Moderately Unable"
    when '3' then "Somewhat Unable"
    when '4' then "Somewhat Able"
    when '5' then "Moderately Able"
    when '6' then "Extremely Able"
    else null
  END as response
FROM table1 corss join col

db fiddle link

Upvotes: 1

Related Questions