0

Apologies for muddy wording on the question, I'm not sure how to concisely capture what I'm trying to do. This is running on SQL Server 2012.

I have a table that looks like this:

Form     Date        Label       Description
-------- ----------- ----------- -----------------
  A      1/17/2018   Food        Apple               
  B      1/16/2018   Food        Celery              
  C      1/15/2018   Food        Pork                
  C      1/15/2018   Topping     Gravy               
  C      1/15/2018   Side        Mashed Potatoes

And I would like to generate a report that looks like this:

Form A    Date: 1/17/2018

    Food        Topping       Side
    ------      --------      ------
    Apple        None          None

Form B    Date: 1/16/2018

    Food        Topping       Side
    ------      --------      ------
    Celery      None          None

Form C    Date: 1/15/2018

    Food        Topping       Side
    ------      --------      ------
    Pork        Gravy         Mashed Potatoes

Is there a way to display these Description values in specific positions when they all share the same column? I've tried making visibility expressions based on the Label column value, but it just hides the field altogether.

I would create a row group on the Label column if I could but in this instance the positions are fixed constraints. Currently the only grouping is the Form row group.

Jared
  • 25
  • 3
  • Form C has several rows for it. Could you show how you'd like those 3 displayed? Is there, also, some kind of identifier field in your data (if not, why not), or are those all the columns – Thom A Jan 17 '18 at 17:09
  • Change the query to use a PIVOT – Mazhar Jan 17 '18 at 17:30
  • I would fiddle around with either the idea that Cool_Br33ze mentioned - `PIVOT` the data or you could play around with adding a column group on the `Label` column and see if you get your results that way. – BJones Jan 17 '18 at 18:55
  • @Larnu Ideally I'd like them displayed on a single row like on the third entry of the example report. There is an ID field as well though, I just omitted it since it didn't seem too relevant to the issue itself. – Jared Jan 17 '18 at 19:15
  • @Cool_Br33ze Thanks, I'll look into that. I really need to just sit down with a SQL textbook cover to cover to be aware of the entire toolkit. – Jared Jan 17 '18 at 19:23
  • Thanks @bjones, I actually did try that initially but ran into problems since I have a row group on Forms already. – Jared Jan 17 '18 at 19:27
  • Found the solution for what I was trying to do [here](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function), after looking into the PIVOT function. I didn't end up using PIVOT here but I have a better understanding now of where it's useful for the future. Thanks all for pointing me in the right direction. – Jared Jan 17 '18 at 21:52

0 Answers0