How to get data of multiple records having a common value of some column, in multiple columns

Question

Hi all, I am having a table in which we keep data of person and different fees paid by him. A person can have multiple number of FeeId associated to him. Now the task is to fetch the multiple records of a person in a single row in below format: PersonId | FeeId1 | Start1 | End1 | Amount1| FeeId2 | Start2 | End2 | Amount2 | FeeId3 | Start3 | End3 | Amount3 | FeeId4 | Start4 | End4 | Amount4 and so on.. ![236684-dataaa.png][1] I tried pivot but as I am new to this concept i was able to fetch only data of one column like: PersonId | FeeId1|FeeId2 | FeeId3 | FeeId4 And moreover the count of columns is static in my query which can be dynamic in real situation select PersonId,fee1,fee2,fee3,fee4 From ( select PersonId,FeeId, 'fee'+cast(ROW_NUMBER() OVER(partition BY PersonId order by PersonId) as varchar(10)) clmsq from [dbo].[PersonFee] ) temp pivot( max(FeeId) for clmsq in (fee1,fee2,fee3,fee4) )piv ![236657-query.png][2] [1]: /api/attachments/236684-dataaa.png?platform=QnA [2]: /api/attachments/236657-query.png?platform=QnA

Answer 1

Maybe it is easier to write a query using a reasonable limit of columns.

However, in this particular case, the dynamic query probably does not look complicated:

declare @c as int  
  
select @c = max(c)  
from (  
    select PersonId, count(distinct FeeId) as c  
    from PersonFee  
    group by PersonId  
) as t  
  
declare @sql varchar(max) = 'select PersonId, '  
  
declare @i int = 1  
  
while @i <= @c  
begin  
  
    if @i <> 1 set @sql += ','  
  
    declare @j varchar(max) = @i  
  
    set @sql += '  
    max(case r when ' + @j + ' then FeeId end) as FeeId'   + @j + ',  
    max(case r when ' + @j + ' then Start end) as Start'   + @j + ',  
    max(case r when ' + @j + ' then [End] end) as End'     + @j + ',  
    max(case r when ' + @j + ' then Amount end) as Amount' + @j  
    set @i += 1  
end  
  
set @sql += '  
from (  
    select *, row_number() over (partition by PersonId order by Start) as r  
    from PersonFee  
) t  
group by PersonId  
order by PersonId  
'  
  
exec (@sql)  

By the way, the sample data contains duplicate FeeId for Person 83, If this is not accidental, then it is not clear how to pivot such data.

Answer 2

Take a look at this article which may help you https://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

Answer 3

This is straightforward, as long as you keep away from the PIVOT keyword.

   WITH numering AS (  
       SELECT *, row_number() OVER(PARTITION BY PersonID ORDER BY FeeID) AS rowno  
      FROM tbl  
   )  
   SELECT PersonId,  
          MIN(CASE rowno WHEN 1 THEN FeeID END) AS FeeId1,  
          MIN(CASE rowno WHEN 1 THEN Start END) AS Start1,  
          MIN(CASE rowno WHEN 1 THEN "End" END) AS End1,  
          MIN(CASE rowno WHEN 1 THEN Amount END) AS Amount1,  
          MIN(CASE rowno WHEN 2 THEN FeeID END) AS FeeId2,  
          MIN(CASE rowno WHEN 2 THEN Start END) AS Start2,  
          MIN(CASE rowno WHEN 2 THEN "End" END) AS End2,  
          MIN(CASE rowno WHEN 2 THEN Amount END) AS Amount2,  
         ...  
   FROM  numbering  
   GROUP BY PersonID  

The MIN may be confusing, but each of these MIN only see one non-NULL value, and you could as just well use MAX. The purpose MIN serve is bring everything into a single row.

Answer 4

We appreciate you sharing this content. It is really beneficial to me, and I require a different account for my Xxxtentacion Revenge merchandise company.