问题描述:

I have two tables 1) Users and 2) Images.

Users table

id name

1 xyz

2 abc

Images table

id path

1 ~/img/12.jpg

1 ~/img/34.jpg

2 ~/img/de.jpg

2 ~/img/rt.jpg

So now when I write the query:

select id, name, path

from users

left join images on images.id = users.id

where id =1

Then it will give me output like:

id name path

1 xyz ~/img/12.jpg

1 xyz ~/img/34.jpg

But I need the output like:

 id name path1 path2

1 xyz ~/img/12.jpg ~/img/34.jpg

How can I get this?

网友答案:

for your output we can achieve using Cross Apply and Pivot

declare @users TABLE 
    (id   INT,      varchar(4))
;

INSERT INTO @users
    (id, name)
VALUES

    (1, 'xyz'),
    (2, 'abc')
;


declare @images  TABLE 
    (id int, path varchar(12))
;

INSERT INTO @images
    (id, path)
VALUES

    (1, '~/img/12.jpg'),
    (1, '~/img/34.jpg'),
    (2, '~/img/de.jpg'),
    (2, '~/img/rt.jpg')
;
select * from (
select s.id, 
s.name , 
images.path,
COL + CAST(row_number()over(PARTITION BY s.ID ORDER BY COL) AS VARCHAR) RN 
from @users s 
left join @images images  
on images.id=s.id
CROSS APPLY (VALUES ('path',path))CS(Col,val)
    where s.id =1)P
    PIVOT (MAX(PATH) FOR RN IN ([path1],[path2]))P 
网友答案:

It is easy when it is fixed to 2 paths

;WITH paths AS
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY path) AS Seq FROM Images
)
SELECT 
  *
  (SELECT path FROM paths WHERE id = u.id AND Seq = 1) Path1, 
  (SELECT path FROM paths WHERE id = u.id AND Seq = 2) Path2 
FROM Users u 
WHERE id = 1
网友答案:

Do a GROUP BY for min and max path:

select u.id, u.name, min(i.path), max(i.path)
from users u
    left join images i on i.id = u.id
group by u.id, u.name

Works well as long as just one or two paths for a user.

网友答案:

You can use Group by like

select id, name , path from users left join images on images.id=users.id
where id =1 group by id
相关阅读:
Top