QEURY HELP: Case when X happens, return INFORMATION_SCHEMA.COLUMNS in comma delimited list

• gdf8285

  Valued Member

  Points: 61

  More actions

  November 22, 2013 at 11:07 am

  #280410

  Hi all and thanks in advance...first post and it might not even be possible, but here goes.

  Example of table dbo.CartSystems below:

  CartID Actuarial Pharmacy SOLAR

  4000 1 0 1

  4001 1 0 0

  4002 0 1 1

  4003 0 0 0

  4004 1 1 1

  What I have right now is

  SELECT DISTINCT C.CartID, STUFF((SELECT ', ' +

  CASE WHEN CS.SoEasi = 1 THEN 'SoEasi'

  WHEN CS.Actuarial = 1 THEN 'Actuarial'

  WHEN CS.Pharmacy = 1 THEN 'Specialty Actuarial'

  WHEN CS.SOLAR = 1 THEN 'SOLAR' END

  FROM dbo.CartSystems CS

  WHERE CS.CartID = C.CartID

  FOR XML PATH (''))

  , 1, 1, '')

  FROM dbo.Cart C

  This gives me my desired output, but there are 30ish systems I'm actually working with and if one gets added or taken away, I have to manually add or delete that line in the code. I was hoping there would be some way to tie in and match the INFORMATION_SCHEMA.COLUMNS to make it dynamic, where I could say, "If C.CartID 4000 has a 1 in any field, pull all fields, concatenate in comma delimited list."

  Not sure if this can even be done, but thanks again in advance.

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  November 22, 2013 at 11:12 am

  #1668490

  Hi and welcome to the forums! It is kind of hard to figure out what you are trying to do.

  In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/