Select return dynamic columns

Question

I have two tables: Standards and Service Offers. A standard may have several service offerings. Each Standard may have a different number of Services associated with it.

What I need to do is write a view that will return some general data and then list the service offers in one line. For instance:

Standard Id | Description | SO #1 | SO #2 | SO #3 | ... | SO #21 | SO Count 1 | One | A | B | C | ... | G | 21 2 | Two | A | | | ... | | 1 3 | Three | B | D | E | ... | | 3 

I do not know how to write it. The number of SO columns is set to a specific number (in this case, 21), so we cannot exceed this.

Any ideas on how to approach this?

The place I started is below. He simply returned a few lines for each service offer when they should be on the same line.

 SELECT * FROM SERVICE_OFFERINGS WHERE STANDARD_KEY IN (SELECT STANDARD_KEY FROM STANDARDS) 

Additional SQL

So, here I have SQL, which returns everything I need, but will return 11 rows due to the presence of 11 service offers. I tried a pivot table and can't figure out how to do this. Can anyone help with some sample code?

 SELECT DISTINCT stpc.standard_key, stpc.test_id, NULL AS pricebook_id, stpc.stabdard_name AS description, stpc.date_start AS begin_date, stpc.date_end AS end_date, sopd.service_offering_id FROM STANDARDS stpc, SERVICE_OFFERINGS sopd WHERE 1=1 AND sopd.standard_key = stpc.standard_key ORDER BY stpc.standard_key, sopd.service_offering_id 

UPDATE

Since the database does not allow PIVOT tables (and could not understand the XML sentence), I had to do a bit of complicated SQL to get it working. Here is what I used:

 select stpc.oracle_product_code AS test_id, CASE WHEN stpc.store_key = 200 THEN 'CE_USAUSD09' WHEN stpc.store_key = 210 THEN 'CE_CANCAD09' END AS pricebook_id, stpc.standard_name AS its_test_desc, CONVERT(VARCHAR(10), stpc.date_start, 101) AS begin_date, CONVERT(VARCHAR(10), stpc.date_end, 101) AS end_date, MAX(CASE WHEN rn = 1 THEN b.service_offering_id END) AS SERVICE_OFFERING_1, MAX(CASE WHEN rn = 2 THEN b.service_offering_id END) AS SERVICE_OFFERING_2, MAX(CASE WHEN rn = 3 THEN b.service_offering_id END) AS SERVICE_OFFERING_3, MAX(CASE WHEN rn = 4 THEN b.service_offering_id END) AS SERVICE_OFFERING_4, MAX(CASE WHEN rn = 5 THEN b.service_offering_id END) AS SERVICE_OFFERING_5, MAX(CASE WHEN rn = 6 THEN b.service_offering_id END) AS SERVICE_OFFERING_6, MAX(CASE WHEN rn = 7 THEN b.service_offering_id END) AS SERVICE_OFFERING_7, MAX(CASE WHEN rn = 8 THEN b.service_offering_id END) AS SERVICE_OFFERING_8, MAX(CASE WHEN rn = 9 THEN b.service_offering_id END) AS SERVICE_OFFERING_9, MAX(CASE WHEN rn = 10 THEN b.service_offering_id END) AS SERVICE_OFFERING_10, MAX(CASE WHEN rn = 11 THEN b.service_offering_id END) AS SERVICE_OFFERING_11, MAX(CASE WHEN rn = 12 THEN b.service_offering_id END) AS SERVICE_OFFERING_12, MAX(CASE WHEN rn = 13 THEN b.service_offering_id END) AS SERVICE_OFFERING_13, MAX(CASE WHEN rn = 14 THEN b.service_offering_id END) AS SERVICE_OFFERING_14, MAX(CASE WHEN rn = 15 THEN b.service_offering_id END) AS SERVICE_OFFERING_15, MAX(CASE WHEN rn = 16 THEN b.service_offering_id END) AS SERVICE_OFFERING_16, MAX(CASE WHEN rn = 17 THEN b.service_offering_id END) AS SERVICE_OFFERING_17, MAX(CASE WHEN rn = 18 THEN b.service_offering_id END) AS SERVICE_OFFERING_18, MAX(CASE WHEN rn = 19 THEN b.service_offering_id END) AS SERVICE_OFFERING_19, MAX(CASE WHEN rn = 20 THEN b.service_offering_id END) AS SERVICE_OFFERING_20, MAX(CASE WHEN rn = 21 THEN b.service_offering_id END) AS SERVICE_OFFERING_21, MAX(rn) AS service_offering_count FROM ( select standard_key, service_offering_id, row_number() over (partition by standard_key order by standard_key) rn from SERVICE_OFFERINGS ) B, SERVICE_OFFERINGS sopd, STANDARDS stpc where b.service_offering_id = sopd.service_offering_id AND b.standard_key = stpc.standard_key AND sopd.standard_key = stpc.standard_key AND stpc.store_key IN (200,210) AND stpc.create_date > '03/29/2010' group by stpc.oracle_product_code,stpc.store_key,stpc.standard_name,stpc.date_start,stpc.date_end 

Answer 1

You can use the PIVOT functionality for this.

Check out http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

Instead of PIVOT, you should use a combination of FOR XML and SplitToColumns.

Use FOR XML and draw your sentences into a single column. Combining row values ​​in Transact-SQL

Then use the CTE style function to split one cell into columns, as shown here http://www.sqlservercentral.com/articles/CTE/67974/

This will give you a table that will be formatted as you need.

Then do arithmetic to get the number of non-zero columns, and you have the score you need at the end.

Answer 2

Yes, key queries are what you need to use.

Is column 21 the same, or can you show no more than 21 columns (out of, say, hundreds)? If the actual columns can vary from query to query, you will need to look into dynamic queries (build the query as a row - including the columns you want to flip), and then execute the row).

Answer 3

Philip is right. If you always have 21 columns, this is a simple summary query. I am inserting a sample code here that you could use. But if the number of columns will vary between 1 and 21, you will have to write a dynamic query.

 SELECT standard_key, stabdard_name, [A] as SO1, [B] as SO2, [C] as SO3, [D] as SO4, [E] as SO5....-- and so on with the other columns FROM (SELECT ST.standard_key, ST.stabdard_name, SO.service_offering_id FROM SERVICE_OFFERINGS SO INNER JOIN STANDARDS ST ON SO.standard_key= ST.standard_key)p PIVOT ( MAX (service_offering_id) FOR service_offering_id IN ( [A], [B], [C], [D], [E]....-- and so on with the other values) ) AS pvt ORDER BY standard_key 

If the numbers can change, you can try something like this:

 declare @sql nvarchar(max) declare @sql2 nvarchar(max) SET @sql2='' set @sql = ' select standard_key, stabdard_name,' select @sql = @sql + '['+ service_offering_id + '] AS [SO' + convert(varchar, Row_number() OVER (ORDER BY service_offering_id))+ '],' from (select distinct [service_offering_id] from [SERVICE_OFFERINGS]) as moduleids select @sql2 = @sql2 + '['+ service_offering_id + '],' from (select distinct [service_offering_id] from [SERVICE_OFFERINGS]) as moduleids set @sql2 = substring(@sql2,1,len(@sql2)-1) set @sql = substring(@sql,1,len(@sql)-1) + ' FROM (SELECT ST.standard_key, ST.stabdard_name, SO.service_offering_id FROM SERVICE_OFFERINGS SO INNER JOIN STANDARDS ST ON SO.standard_key= ST.standard_key)p PIVOT ( MAX (service_offering_id) FOR service_offering_id IN (' + @sql2 + ')) AS pvt ORDER BY standard_key' print @sql exec sp_executesql @sql