0

I want to convert multiple rows to multiple columns in sql server 2017 (v17.6). I use this query:

select
    CapturedFrame.capturedFrame_id,
    CapturedFrame.fileName, 
    LicensePlate.licensePlate_id,
    CharacterName.characterName_id, 
    CharacterName.name
from
    CapturedFrame
    join LicensePlate on CapturedFrame.capturedFrame_id = LicensePlate.capturedFrame_id
    join Character on LicensePlate.licensePlate_id = Character.licensePlate_id
    join CharacterName on Character.characterName_id = CharacterName.characterName_id
order by
    licensePlate_id, x0

Obtained result is depicted below:

before pivoting

I want to get results as below:

after pivoting

In other words, I want to group each 8 rows according to lp_id and add ch_id# and name# (# is a number from 1 to 8) as columns.

How can I get such result?

Babak.Abad
  • 2,839
  • 10
  • 40
  • 74
  • 1
    Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query)? – Thom A Feb 09 '19 at 11:43
  • @Lamu - It's not, I'm afraid. A Pivot won't work here because pivot relies on the values in the columns ; in this dataset you could pivot to get columns called [40], [34], [6] etc - the contents of the ch_id column, as well as the contents of the name column but I don't think that's what is asked for. I'm still thinking about how it could be accomplished. – simon at rcl Feb 09 '19 at 12:49
  • Looking at the requested output there appears to be an extra pair of columns: ch_id and name whose values repeat what's in ch_id1 and name1 and result in 9 pairs of columns not 8. Is this just a mistake? Also, what governs which ch_id is in ch_id1, ch_id2 etc? That is, is the order if the values important? (You appear to have them in the order they appear in the base dataset; is that required?) – simon at rcl Feb 09 '19 at 12:59
  • @simonatrcl Excuse me for 9 pairs of columns in image. I edit it. You can imagine that `ch_id#` is a code for `name#`. For example, You can imagine the relation between `city_id` and `city_name`. I also update the query showing that order of `ch_id#` and `name#` is according to 'licensePlate_id` and then by `x0`. – Babak.Abad Feb 09 '19 at 16:28
  • You should make it more readable, I can not see what is expected result. And I don't properly get what you want. All I see is data is group by ip_id but what are the basis for 8 groups? – abdul qayyum Feb 09 '19 at 17:03
  • DDL along with sample data (INSERTs) would be really useful for a question like this, do you really think people want to help you if you can't be bothered to make it easy for them? https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – MJH Feb 09 '19 at 18:53

0 Answers0