question

SahirShah-5832 avatar image
0 Votes"
SahirShah-5832 asked MelissaMa-msft commented

MS Access . Pivot row values into column headers

Hi,

I have an extract from the Iris dataset with just the species and petal_length

species, petal_length
setosa, 1.4
setosa, 1.4
setosa, 1.3
setosa, 1.5
setosa, 1.4
versicolor, 4.7
versicolor, 4.5
versicolor, 4.9
versicolor, 4
versicolor, 4.6
virginica, 5.1
virginica, 5.9
virginica, 5.6
virginica, 5.8
virginica, 6.6

There are exactly 5 values for each species. How can I pivot this and convert it into the following form with the three species as column headers and the corresponding petal_width as rows. I can write a VBA program to achieve this, but that seems like an a cumbersome solution. Is there an SQL query (either in MS Access or SQL server) to achieve this ?

121549-image.png


sql-server-generalsql-server-transact-sqloffice-access-dev
image.png (5.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's to less on information and I don't see any relation between the figures. E.g how are the figures in the first row related to?

Please post table design as DDL, some sample data as DML statement and the expected result.

0 Votes 0 ·

I think that the data are unordered inside Access, and it is difficult to associate the rows. It will be easier if you have an additional column (Col1) like this:

Col1, species, petal_length
1 setosa, 1.4
2 setosa, 1.4
3 setosa, 1.3
4 setosa, 1.5
5 setosa, 1.4
1 versicolor, 4.7
2 versicolor, 4.5
3 versicolor, 4.9
4 versicolor, 4
5 versicolor, 4.6
1 virginica, 5.1
2 virginica, 5.9
3 virginica, 5.6
4 virginica, 5.8
5 virginica, 6.6

Or like this:

Col1, species, petal_length
1 setosa, 1.4
2 setosa, 1.4
3 setosa, 1.3
4 setosa, 1.5
5 setosa, 1.4
6 versicolor, 4.7
7 versicolor, 4.5
8 versicolor, 4.9
9 versicolor, 4
10 versicolor, 4.6
11 virginica, 5.1
12 virginica, 5.9
13 virginica, 5.6
14 virginica, 5.8
15 virginica, 6.6

The values of Col1 do not matter in the latter case, but they must be unique.

0 Votes 0 ·

Hi @SahirShah-5832,

Could you please validate the answer and provide any update?

If it is not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @SahirShah-5832,

Welcome to Microsoft Q&A!

In SQL Server, please refer below:

 create table tablesa
 (species varchar(20),
 petal_length decimal(2,1))
    
 insert into tablesa values
 ('setosa', 1.4),
 ('setosa', 1.4),
 ('setosa', 1.3),
 ('setosa', 1.5),
 ('setosa', 1.4),
 ('versicolor', 4.7),
 ('versicolor', 4.5),
 ('versicolor', 4.9),
 ('versicolor', 4),
 ('versicolor', 4.6),
 ('virginica', 5.1),
 ('virginica', 5.9),
 ('virginica', 5.6),
 ('virginica', 5.8),
 ('virginica', 6.6)
    
 select [setosa],[versicolor],[virginica] from (
 select *,ROW_NUMBER() over (partition by species order by (select 1)) rn from tablesa) s
 pivot 
 (max(petal_length) for species in ([setosa],[versicolor],[virginica])) p

OR

 select [setosa],[versicolor],[virginica] from (
 select rn, 
 max(case when species = 'setosa' then petal_length end) as setosa,
 max(case when species = 'versicolor' then petal_length end) as versicolor,
 max(case when species = 'virginica' then petal_length end) as virginica
 from (select *,ROW_NUMBER() over (partition by species order by (select 1)) rn from tablesa) a
 group by rn) b

OR in dynamic way as below:

 declare @sql nvarchar(max)
        
 select @sql=STUFF(( SELECT distinct  ',['+species+']'  FROM tablesa FOR XML PATH('') ), 1, 1, '')
    
 set @sql=N' select '+@sql+' from (
 select *,ROW_NUMBER() over (partition by species order by (select 1)) rn from tablesa) s
 pivot 
 (max(petal_length) for species in ('+@sql+')) p'
        
 EXECUTE sp_executesql  @sql

Output:

 setosa versicolor virginica
 1.4 4.7 5.1
 1.4 4.5 5.9
 1.3 4.9 5.6
 1.5 4.0 5.8
 1.4 4.6 6.6

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.