0

I have a data set that has multiple record per customer. Each row has a model number. I want to turn all model numbers in the set into columns to calculate the number of models each customer has along with a total. I can do this now by hard coding the model numbers.But I would rather this be a dynamic and grow as camera models added or dropped. Any help or insight would be greatly appreciated

Starting Table:

Customer #    Model#    Mac #
123           Cam320     1234bbfgdv
123           Cam320     12567fkrl4
453           Cam110     574446nng3
453           Cam880     578455mmh3
453           Cam320     445445rtsy

Output

Customer #  Cam110  Cam320    Cam880   Total
123           0        2         0      2
453           1        1         1      3
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
B F
  • 11
  • 2

1 Answers1

1

Although this can be done with T-SQL via the PIVOT function, this functionality is best left to a report engine. SQL Server is built to store data, not necessarily display it, and your total at the end would be challenging to make dynamic. This data set is already perfect for an SSRS report. I would recommend trying SSRS. However, if you're determined on using T-SQL, check out this post. Dynamically create columns sql

Mike Petri
  • 570
  • 3
  • 10
  • 1
    To Mike's point, you can create dynamic SQL with PIVOT to have the number of columns grow dynamically with the models, but that would change the schema of the table causing downstream issues for anything consuming it. This would be more appropriate for a visualization tool. – Andrew O'Brien Jun 08 '21 at 18:31