Sql-server – Query 2 tables one with 1 row and the other with multiple rows

group bysql server

I have a part table:

ID,      Description
ABC-123  ABC
DEF-456  DEF

I have a User Defined Fields table that stores information in a single row.

ID,         DOCUMENTID, STRING_VAL,         DATE_VAL
UDF-000021, ABC-123,    TEXT123,            NULL
UDF-000022, ABC-123,    NULL,               6/10/2016
UDF-000023, ABC-123,    JOHN WORKING ON IT, NULL
UDF-000024, ABC-123,    NULL,               7/1/2016
UDF-000025, ABC-123,    YES,                NULL
UDF-000021, DEF-456,    TEXT123,            NULL
UDF-000022, DEF-456,    NULL,               6/10/2016

I need the information to look like this:

PART ID  DESCRIPTION    REVISION     REVISION DATE   NOTES    MARKUP DATE   IM
  -------------------------------------------------------------------------------
  ABC-123  ABC            TEXT123      6/10/2016       JOHN     7/1/2016      YES
  DEF0456  DEF            TEXT123      6/10/2016

I have looked at joins, pivot, etc. and I guess I just cant seem to wrap my head around it. Any help is greatly appreciated.

Best Answer

You can use the old fashioned PIVOT approach as you need to aggregate two different columns.

WITH Pivoted AS
(
SELECT DOCUMENTID,
       REVISION = MAX(CASE WHEN ID = 'UDF-000021' THEN STRING_VAL END),
       [REVISION DATE] = MAX(CASE WHEN ID = 'UDF-000022' THEN DATE_VAL END),
       NOTES = MAX(CASE WHEN ID = 'UDF-000023' THEN STRING_VAL END),
       [MARKUP DATE] = MAX(CASE WHEN ID = 'UDF-000024' THEN DATE_VAL END),
       IM = MAX(CASE WHEN ID = 'UDF-000025' THEN STRING_VAL END)
FROM UserDefinedFields
GROUP BY DOCUMENTID
)
SELECT p.ID,
       p.Description,
       pv.REVISION,
       pv.[REVISION DATE],
       pv.NOTES,
       pv.[MARKUP DATE],
       pv.IM
FROM   Part p
       LEFT JOIN Pivoted pv
         ON p.ID = pv.DOCUMENTID

Online Demo

Related Solutions

Sql-server – Merge multiple rows into one row with sql

SELECT [Employee]
      ,[Year]
      ,[Period]
      ,[Activity]
      ,Sum([Day1]) as Day1
      ,Sum([Day2]) as Day2
      ,Sum([Day3]) as Day3
      ,Sum([Day4]) as Day4
      ,Sum([Day5]) as Day5
      ,Sum([Day6]) as Day6
      ,Sum([Day7]) as Day7
FROM [HoursAccounting]
where Employee = '1234'
group by Employee, [Year], Period, Activity;

Mysql – Grouping multiple records into one row

If the number of categories is fixed an known in advance, this is just some conditional summing over your existing solution... and add whatever logic is needed for the visits via a subquery:

SELECT
    date,
    sum(CASE WHEN `categoryId`=1 THEN `ticketCount` END) AS `Category A`,
    sum(CASE WHEN `categoryId`=2 THEN `ticketCount` END) AS `Category B`,
    sum(CASE WHEN `categoryId`=3 THEN `ticketCount` END) AS `Category C`,
    (SELECT  count(id) FROM event_page_visits 
      WHERE event_id = 1 and date(created_at) = date) AS `Visits`
FROM
(
    select 
      `list_of_dates`.`date` as `date`, `ticket_categories`.`id` as `categoryId`, `ticket_categories`.`name` as `categoryName`, count(tickets.id) as ticketCount 
    from ( 
      SELECT DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) AS DATE FROM seq_0_to_999 AS seq WHERE DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) <= '2017-03-14' 
    ) as list_of_dates 
    cross join 
      `ticket_categories` 
    left join 
      `tickets` on 
        `tickets`.`ticket_category_id` = `ticket_categories`.`id` and 
        `tickets`.`created_at` >= `list_of_dates`.`date` and 
        `tickets`.`created_at` < list_of_dates.date + INTERVAL 1 DAY
    where 
      `ticket_categories`.`event_id` = 1
    group by 
      `date`, `ticket_categories`.`id`
) AS q
GROUP BY 
   `date`
order by 
  `date` asc ;

And you just get:

|       date | Category A | Category B | Category C | Visits |
|------------|------------|------------|------------|--------|
| 2017-03-10 |          4 |          3 |          2 |     10 |
| 2017-03-11 |          0 |          0 |          0 |      2 |
| 2017-03-12 |          1 |          1 |          0 |      3 |
| 2017-03-13 |          0 |          0 |          3 |      6 |
| 2017-03-14 |          0 |          0 |          0 |      0 |

Fiddle.

Best Answer

Related Solutions

Sql-server – Merge multiple rows into one row with sql

Mysql – Grouping multiple records into one row

Related Question