Dynamically pivot a table
-
Hello,
Seeking help to dynamically pivot a table using a date field in a table.
Current structure
acctid_prodId dates
123asd 1/1/2018
123asd 2/1/2018
123asd 3/1/2018
123asd 4/1/2018
123asd 5/1/2018
123asd 6/1/2018
123asd 7/1/2018
123asd 8/1/2018
123asd 9/1/2018
456zxc 1/1/2018
456zxc 2/1/2018
456zxc 3/1/2018
456zxc 4/1/2018
456zxc 5/1/2018
456zxc 6/1/2018
456zxc 7/1/2018
456zxc 8/1/2018
456zxc 9/1/2018
789vbn 1/1/2018
789vbn 2/1/2018
789vbn 3/1/2018
789vbn 4/1/2018
789vbn 5/1/2018
789vbn 6/1/2018
789vbn 7/1/2018
789vbn 8/1/2018
789vbn 9/1/2018New structure
1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 9/1/2018
123asd 1 1 1 1 1 1 1 1 1
456zxc 1 1 1 1 1 1 1 1 1
789vbn 1 1 1 1 1 1 1 1 1Thank you for your help
- People are much more inclined to help if they see that you've actually attempted this yourself.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
fair point.. unfortunately, I am not as advanced. Not sure where to start. I built a dynamic excel model that works well but not efficient. Looking to get input from people who are more advanced that I am. Any help is greatly appreciated.
- alexander.lummer - Tuesday, September 4, 2018 12:24 PM
Read the link I provided. Practice all the steps. The dynamic stuff is at the end, but if you practice all the steps provided, you'll understand what you're doing.
- alexander.lummer - Tuesday, September 4, 2018 12:24 PM
Why do you believe you need a dynamic pivot? From the data you posted - it looks like you want YTD which will have at most 12 months where you will always have the same columns (January through December) but for a different year.
It would be much easier to label your columns by the month name and include all 12 months every time even if the last 3 months are blank instead of labeling each column with the month/day and year.
If you needed to include additional years - then you would add an additional column for the year, keeping the same 12 columns for each month.
If using the data in Excel or SSRS or another downstream reporting system - you can easily modify the column headers depending on when the report is run or what parameters are used to generate the results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
My table is dynamic as it keeps a track of retained/loss/gained products for any given client globally. We use this information to gain a deeper understanding of our clients purchasing Behavior as well as identify any potential flows in our product mix. Unless there is a better way, my current output is a concat field of account_id/prodid (row) by dates (columns).
I would be very happy to reduce the size of the table to just 12 columns but I am not sure where you would insert the years assuming the structure i described above.
Again, any help is greatly appreciated.
- Brandie Tarvin - Tuesday, September 4, 2018 12:29 PM
Got it to work. Took just about 10 min run. The script summarized the table by date only. where in the code do i add my rows? unique value of account_id/prod_id
- alexander.lummer - Tuesday, September 4, 2018 1:36 PM
You do need a unique identifier to pivot correctly. You should play with the columns you have available. if they aren't working for you, add an identity column for an additional pivot and see what that gets you.
Edit: Part of my issue with assisting you is that your expected results don't come from your sample data. You don't have all those 1s anywhere in the data. So I don't know what you're doing, where you're pulling from and what you really need. In the future, you should provide DDL statements (create table, etc.) with an INSERT script for your sample data so that anyone helping you can test code before they post it to your question thread.
- Brandie Tarvin - Wednesday, September 5, 2018 5:44 AM
Thank you for you feedback. I attached an image of the output. I ran your code with modification to handle my objective. As you can see below, I have a unique ID with months for columns. The script marks whether an account id existed in a given month with 1 and 0 if it did not. Objective is achieved BUT its running a little long.. roughly 10 min or so. Any optimization suggestions are welcome. I've attached the augmented code below. Thank you again.
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(list_dates)
FROM (SELECT distinct list_dates from vw_active_program_product where list_dates <= eomonth(getdate())) AS x; ---vw_active_program_product table pulled from SFDC db
SET @sql = N'
create view vw_progprod_gain_loss as
SELECT unique_id, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT unique_id, account_id, list_dates
FROM vw_active_program_product
) AS j
PIVOT
(
count(account_id) FOR list_dates IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply