I have an extensive product database in SQL Server. I need to take 3 tables and combine them so that I have at the end a table with columns named after items of one table, with productid as rows with the field data concatenated data from 2 other tables. No mathematical functions are performed at all. Data is just collated.
1) Table 1 pdt_WebWidgets has 132 items I call "Widgets." Column names for the pivot crosstab will come from field labeltext. New widgets are occasionally added, so pivot must be able to include these new widgets as they are created.
2) Table 2 pdt_WebWidgetValue has over 7 million records, with fields ProductID, WidgetID, Value. pdt_WebWidgetValue.WidgetID=pdt_WebWidgetID
3) For [login to view URL] IN (2,6), we can join pdt_WebWidgetListBox (57,000 records) to pdt_WebwidgetValue ON
pdt_WebWidgetListBox.ID=[login to view URL]
pdt_Webwidgetlistbox.WidgetID=[login to view URL]
Again, this is only for items in Type=(2,6). For this WidgetType, we will concatenate the values in [login to view URL] as a comma-delimited list in the pivot fields. All other Types, we will just take the actual pdt_WebWidgetValue.Value. These should be single values, typically.
I already have a query that does this, but it can be very slow. I use a number of static temp tables to help build the data to get it ready and make it easier/faster for the crosstab to be built. I wanted to see if someone could design something that would run much faster, creating a static pivot table after collating the data, so I could reduce a number of steps in a SQL Server Agent Job.
Hello, I can for sure build your pivot query in most efficient way. Table of 7 million rows shouldn't represent such a big issue if properly indexed but as you want items to be the columns and columns must be defined i Daha Fazla
Bu iş için 11 freelancer ortalamada $161 teklif veriyor
Hello. How are you? I am highly interested to work on the "Advanced CrossTab/Pivot Query SQL Server". I am a full stack website developer. I have more than 7 years experience in "Microsoft SQL Server, SQL". I will Daha Fazla