SQL Server How to get my joined data into a column per month with multiple rows per month for each user?

u0sqgete 于 2024-01-05 发布在其他

关注(0)|答案(2)|浏览(89)

I'm trying to get a specific result but so far my attempts give an undesired result or an error.

SELECT u.Id 
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '01' AND u.Id = c.Id) 'Month_1_Content'
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '02' AND u.Id = c.Id) 'Month_2_Content'
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '03' AND u.Id = c.Id) 'Month_3_Content'     
  FROM Users u;

Gives error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I also tried

SELECT u.Id 
      ,c.Content 'Month_1_Content'
      ,d.Content 'Month_2_Content'
      ,e.Content 'Month_3_Content'
  FROM Users u
  LEFT OUTER JOIN Content c 
    ON c.Month = '01' AND u.Id= c.Id
  LEFT OUTER JOIN Content d 
    ON d.Month = '02' AND u.Id= d.Id
  LEFT OUTER JOIN Content e 
    ON e.Month = '03' AND u.Id= e.Id

But this query does not return the aimed for result.

Query Result that I want to get

-------------------------------------------------------------
   Id   | Month_1_Content | Month_2_Content | Month_3_Content
-------------------------------------------------------------
 scott  |    01_scott_1   |    02_scott_1   |
 scott  |    01_scott_2   |                 |
 tiger  |                 |    02_tiger_1   |    03_tiger_1   
 tiger  |                 |    02_tiger_2   |    03_tiger_2  
 tiger  |                 |                 |    03_tiger_3   
 cat    |                 |                 | 
-------------------------------------------------------------

Sample data

Table Users

Users
-----
 Id
-----
scott
tiger
cat
-----

Table Contents

Contents
--------------------------------------
Month |   Id   | Sequence |  Content
--------------------------------------
01    | scott  |    1     | 01_scott_1
01    | scott  |    2     | 01_scott_2
02    | scott  |    1     | 02_scott_1
02    | tiger  |    1     | 02_tiger_1
02    | tiger  |    2     | 02_tiger_2
03    | tiger  |    1     | 03_tiger_1
03    | tiger  |    2     | 03_tiger_2
03    | tiger  |    3     | 03_tiger_3
--------------------------------------

sql-server

来源：https://stackoverflow.com/questions/77698386/how-to-get-my-joined-data-into-a-column-per-month-with-multiple-rows-per-month-f

2条答案

按热度按时间

5lwkijsr1#

Your immediate problem is the rather clear error message Subquery returned more than 1 value which quite obviously means you have a subquery that is returning multiple values.

You could make it return a single value, using TOP 1 or some form of aggregation, but that will not get you your desired results.

What you actually want is to pivot on Id, Sequence and return Content per Month .

You can use the PIVOT operator, but the best way to pivot is to manually pivot using MAX(CASE

SELECT
  u.Id ,
  c.*
FROM Users u
OUTER APPLY (
    SELECT
      MAX(CASE WHEN c.Month = '01' THEN c.Content END) AS Month_1_Content,
      MAX(CASE WHEN c.Month = '02' THEN c.Content END) AS Month_2_Content,
      MAX(CASE WHEN c.Month = '03' THEN c.Content END) AS Month_3_Content
    FROM Content c 
    WHERE u.Id = c.Id
    GROUP BY
      c.Sequence
) c;

APPLY is often a good way to pre-aggregate, although you can do this with normal joins also

SELECT
  u.id,
  c.*
FROM Users u
LEFT JOIN (
    SELECT
      c.id,
      MAX(CASE WHEN c.Month = '01' THEN c.Content END) AS Month_1_Content,
      MAX(CASE WHEN c.Month = '02' THEN c.Content END) AS Month_2_Content,
      MAX(CASE WHEN c.Month = '03' THEN c.Content END) AS Month_3_Content
    FROM Content c
    GROUP BY
      c.Id,
      c.Sequence
) c ON u.Id = c.Id;

db<>fiddle

赞(0）回复(0）举报 2024-01-05

xqkwcwgp2#

A simple answer to your question (how to avoid the error), but you may have underlying data issues that make the following solution ineffective. That's up to you to determine.

Add "TOP 1" to your subqueries to ensure only one result is returned for each.

SELECT u.Id 
      ,(SELECT TOP 1 c.Content FROM Contents c WHERE c.Month = '01' AND u.Id = c.Id) 'Month_1_Content'
      ,(SELECT TOP 1 c.Content FROM Contents c WHERE c.Month = '02' AND u.Id = c.Id) 'Month_2_Content'
      ,(SELECT TOP 1 c.Content FROM Contents c WHERE c.Month = '03' AND u.Id = c.Id) 'Month_3_Content'     
  FROM Users u;

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server How to get my joined data into a column per month with multiple rows per month for each user?

2条答案

相关问题

热门标签

最新问答