CODEWITHSUNDEEP
sqldynamictranspose
B F
B F

Reputation: 11

Convert X number of rows into columns for count SQL

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

Upvotes: 0

Views: 373

Answers (1)

Mike Petri
Mike Petri

Reputation: 605

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

Upvotes: 1

Related Questions