asked
Output Row String Based on Column Values
Hi there, I have a very long table, simple example below, that contains a customer number then flagged columns and a seperate column for each that contains a price. How can I output a string of columns and their prices that is composed of all columns flagged as 'Y'? i.e. cowboy_num hat hat_price jacket jacket_price shirt shirt_price pants pants_price boots boots_price 1 Y 10.00 Y 15.00 Y 20.00 Y 12.00 Y 30.00 2 N NULL N NULL Y 20.00 N NULL Y 30.00 3 N NULL N NULL N NULL N NULL N NULL would return: "Cowboy: 1, Hat: 10:00, Jacket: 15.00, Shirt: 20.00, Pants: 12.00, Boots: 30.00" "Cowboy: 2, Shirt: 20.00, Boots: 30.00" "Cowboy: 3, Nothing bought" Due to the number of columns in the real table I am looking for a solution that doesn't use the a large complex CASE statement that would need amended each time a new column is added. TIA
Thanks for this. Unfortunately I'm still struggling to get it to execute correctly on either 2000 or 2008. I changed the appropriate sys columns and tables and inserted the new select query however this time I'm getting an error "Incorrect syntax near '+'". I can only assume that this has to do with the dynamic sql and in particular a missing or erroneous ' but I'm darned if I can locate it this morning. Did it run ok on your 2005 instance?
This is one nightmare of dynamic sql. It allows you to add more columns, but it makes the assumption that your columns are **always** paired like this and named consistently: boot, boot_price; hat, hat_price; etc. USE tempdb CREATE TABLE cowboytest (cowboy_num INT, Hat BIT, hat_price MONEY, Jacket BIT, jacket_price MONEY, Shirt BIT, shirt_price MONEY, Pants BIT, pants_price MONEY, Boots BIT, boots_price MONEY) INSERT cowboytest VALUES (1, 1, 10, 1, 15, 1, 20, 1, 12, 1, 30) INSERT cowboytest VALUES (2, 0, NULL, 0, NULL, 1, 20, 0, NULL, 1, 30) INSERT cowboytest VALUES (3, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL) DECLARE @query NVARCHAR(4000) SELECT @query = STUFF(( SELECT query + '' FROM ( SELECT 'SELECT CASE WHEN len(src.PurchaseMessage) BETWEEN 0 AND 13 THEN PurchaseMessage + '', Nothing bought'' ELSE PurchaseMessage END FROM cowboytest as ct1 INNER JOIN (SELECT cowboy_num, ''Cowboy: '' + convert(varchar, cowboy_num) ' AS query UNION ALL SELECT ' + CASE WHEN ' + sc.name + ' = 1 THEN coalesce('', ' + sc.name + ': '' + convert(varchar, [' + sc2.name + ']), '''') ELSE '''' END' AS query FROM sys.columns AS sc INNER JOIN sys.tables AS st ON sc.object_id = st.object_id INNER JOIN sys.columns AS sc2 ON sc2.name LIKE sc.NAME + '|_price' ESCAPE '|' WHERE st.name = 'cowboytest' UNION ALL SELECT ' AS PurchaseMessage FROM cowboytest) as src on ct1.cowboy_num = src.cowboy_num' )AS bob FOR XML PATH ('')), 1, 0, '') EXEC sp_executesql @query DROP TABLE cowboytest When I run this I get the following output: Cowboy: 1, Hat: 10.00, Jacket: 15.00, Shirt: 20.00, Pants: 12.00, Boots: 30.00 Cowboy: 2, Shirt: 20.00, Boots: 30.00 Cowboy: 3, Nothing bought
SQL Server 2000!!! I forget they didn't add PATH() to FOR XML until SQL 2005. I don't have a SQL 2000 instance handy, but the following doesn't use FOR XML at all. You'll just have to fix the sys.tables and sys.columns references: set @query = '' select @query = @query + ' + CASE WHEN ' + sc.name + ' = 1 THEN coalesce('', ' + sc.name + ': '' + convert(varchar, [' + sc2.name + ']), '''') ELSE '''' END' FROM sys.columns AS sc INNER JOIN sys.tables AS st ON sc.object_id = st.object_id INNER JOIN sys.columns AS sc2 ON sc2.name LIKE sc.NAME + '|_price' ESCAPE '|' WHERE st.name = 'cowboytest' Just replace the `SELECT @query =` query with this (be sure to initialize with the empty string. If @query starts as NULL, it won't work). Let me know if this one works!
If you add a new column for each product, you are in large complex CASE statement territory, though you might be able to put something together using dynamic sql. As an alternative to your current table design, I would suggest having one table for the customer, then another for clothing items the customer has purchased. Whenever the customer purchases a new product, you just add a row to the purchases table and don't have to alter the table. With the two tables you can put together your list of purchased products using the for xml path trick. @ThomasRushton has kindly blogged the technique here - [
http://thelonedba.wordpress.com/2011/02/22/string-concatenation-using-xml-path/][1] Here is a working sample the provides the output using two tables: create table #customer (cowboy_num int, other_columns varchar(150)) create table #purchased_items (cowboy_num int, item_name varchar(150), item_price money) insert #customer values (1, 'values') insert #customer values (2, 'values') insert #customer values (3, 'values') insert #purchased_items values (1, 'Hat', 10.00) insert #purchased_items values (1, 'Jacket', 15.00) insert #purchased_items values (1, 'Shirt', 20.00) insert #purchased_items values (1,'Pants', 12.00) insert #purchased_items values (1,'Boots', 30.00) insert #purchased_items values (2, 'Shirt', 20.00) insert #purchased_items values (2,'Boots', 30.00) SELECT DISTINCT 'Cowboy: ' + CONVERT(VARCHAR, c.cowboy_num) + COALESCE(STUFF(( SELECT ', ' + item_name + ': ' + CONVERT(VARCHAR, item_price) FROM #purchased_items WHERE cowboy_num = c.cowboy_num FOR XML PATH('') ), 1, 0, ''), ', Nothing bought') FROM #customer AS c LEFT JOIN #purchased_items AS p ON c.cowboy_num = p.cowboy_num DROP TABLE #Customer DROP TABLE #purchased_items When I run the sample, I get the following output: Cowboy: 1, Hat: 10.00, Jacket: 15.00, Shirt: 20.00, Pants: 12.00, Boots: 30.00 Cowboy: 2, Shirt: 20.00, Boots: 30.00 Cowboy: 3, Nothing bought [1]:
http://thelonedba.wordpress.com/2011/02/22/string-concatenation-using-xml-path/
Many thanks, certainly a few drinks for your impressive script. :) Although it certainly works on SQL2008 unfortunately, just not so on the SQL2000 legacy system I need. :( I've tried changing the sys.columns to syscolumns and sys.tables to sysobjects, the object_id reference to id, and removed the STUFF function and XML reference but struggling to get it to execute successfully on 2000. My results report a Subquery returned more than 1 value error.
SQL Server 2000!!! I forget they didn't add PATH() to FOR XML until SQL 2005. I don't have a SQL 2000 instance handy, but the following doesn't use FOR XML at all. You'll just have to fix the sys.tables and sys.columns references: set @query = '' select @query = @query + ' + CASE WHEN ' + sc.name + ' = 1 THEN coalesce('', ' + sc.name + ': '' + convert(varchar, [' + sc2.name + ']), '''') ELSE '''' END' FROM sys.columns AS sc INNER JOIN sys.tables AS st ON sc.object_id = st.object_id INNER JOIN sys.columns AS sc2 ON sc2.name LIKE sc.NAME + '|_price' ESCAPE '|' WHERE st.name = 'cowboytest' Just replace the `SELECT @query =` query with this (be sure to initialize with the empty string. If @query starts as NULL, it won't work). Let me know if this one works!
I thought I's post what I'm now trying to execute in SQL2000 but is erroring the "incorrect syntax near '+' message: USE tempdb DROP TABLE cowboytest CREATE TABLE cowboytest ( cowboy_num INT, Hat BIT, hat_price MONEY, Jacket BIT, jacket_price MONEY, Shirt BIT, shirt_price MONEY, Pants BIT, pants_price MONEY, Boots BIT, boots_price MONEY) INSERT cowboytest VALUES (1, 1, 10, 1, 15, 1, 20, 1, 12, 1, 30) INSERT cowboytest VALUES (2, 0, NULL, 0, NULL, 1, 20, 0, NULL, 1, 30) INSERT cowboytest VALUES (3, 0, NULL, 0, NULL, 0, NULL, 0, NULL, 0, NULL) DECLARE @query NVARCHAR(4000) set @query = '' select @query = @query + ' + CASE WHEN ' + sc.name + ' = 1 THEN coalesce('', ' + sc.name + ': '' + convert(varchar, [' + sc2.name + ']), '''') ELSE '''' END' FROM syscolumns AS sc INNER JOIN sysobjects AS st ON
sc.id =
st.id INNER JOIN syscolumns AS sc2 ON sc2.name LIKE sc.NAME + '|_price' ESCAPE '|' WHERE st.name = 'cowboytest' EXEC sp_executesql @query -- edit to add after KenJ comment: Brilliant thanks that's amazing dynamic sql at work. It now works after I added the missing part. You're a genius Sir. Many drinks on order next time I'm in Nebraska. :)
I left out a couple lines when I copied/pasted. Add this before the sp_executesql line. It adds the `SELECT` and the `FROM` that were originally added in via `UNION ALL`: select @query = 'SELECT CASE WHEN len(src.PurchaseMessage) BETWEEN 0 AND 10 THEN PurchaseMessage + '', Nothing bought'' ELSE PurchaseMessage END FROM cowboytest as ct1 INNER JOIN (SELECT cowboy_num, ''Cowboy: '' + convert(varchar, cowboy_num) ' + @query + ' AS PurchaseMessage FROM cowboytest) as src on ct1.cowboy_num = src.cowboy_num'
16 People are following this question.
