如何将列转换为具有相同主键ID的行 [英] how to Convert columns into rows with same primary key id

问题描述

我有一条记录：



id member_photo1 member_photo2 member_photo3

223 photo1.jpg photo2.jpg photo3.jpg



我希望将其转换为



id member_photo

223 photo1.jpg

223 photo2.jpg

223 photo3.jpg



如何在Sql server中执行此操作

解决方案

 选择 id，member_photo1  as  member_photo 
 来自 TblNm 
 
 联盟 < span class =code-keyword> all  
 
  select  id，member_photo2  as  member_photo 
 来自 TblNm 
 
  union  < span class =code-keyword> all  
 
 选择 id，member_photo3 为 member_photo 
 来自 TblNm 

快乐编码！

：）

Solution1（由Aarti提供）非常好。另一种方法是使用 CTE [ ^ ]。

试试这个：

  DECLARE   @ tbl   TABLE （id  INT ，member_photo1  NVARCHAR （ 30 ），member_photo2  NVARCHAR （ 30 ），member_photo3  NVARCHAR （ 30 ））
 
  INSERT   INTO   @ tbl （id，member_photo1，member_photo2，member_photo3）
  VALUES （ 223 ，'  photo1.jpg'，'  photo2.jpg'，'  photo3.jpg'）
  INSERT   INTO   @ tbl （id，member_photo1，member_photo2，member_photo3）
 < span class =code-keyword> VALUES （ 224 ，'  photo10.jpg'，'  photo20.jpg'，< span class =code-string>'  photo30.jpg'）
  INSERT   INTO   @ tbl （id，member_photo1，member_photo2，member_photo3）
  VALUES （ 225 ，'  photo100.jpg'，'  photo200.jpg'，'  photo300.jpg'）
  INSERT   INTO   @ tbl （id，member_photo1，member_photo2，member_photo3）
  VALUES （ 226 ，'  photo1000.jpg'，'  photo2000.jpg' ，'  photo3000.jpg'）
 
  SELECT  * 
  FROM   @ tbl  
 
;  WITH  MyMembers  AS  
（
  SELECT  id，member_photo1  AS  PhotoName 
  FROM   @ tbl  
  UNION   ALL  
  SELECT  id，member_photo2  AS  PhotoName 
  FROM   @ tbl  
  UNION  所有 
  SELECT  id，member_photo3  AS  PhotoName 
  FROM   @ tbl  
）
  SELECT  * 
  FROM  MyMembers 
  ORDER   BY  id 

结果：

 223 pho to1.jpg 
 223 photo2.jpg 
 223 photo3.jpg 
 224 photo30.jpg 
 224 photo20.jpg 
 224 photo10.jpg 
 225 photo100。 jpg 
 225 photo200.jpg 
 225 photo300.jpg 
 226 photo3000.jpg 
 226 photo2000.jpg 
 226 photo1000.jpg 

I have one record as:

id member_photo1 member_photo2 member_photo3
223 photo1.jpg photo2.jpg photo3.jpg

and i want to convert it as

id member_photo
223 photo1.jpg
223 photo2.jpg
223 photo3.jpg

How to do this in Sql server

解决方案

select id, member_photo1 as member_photo 
from TblNm

Union all

select id, member_photo2 as member_photo 
from TblNm

union all

select id, member_photo3 as member_photo 
from TblNm

Happy Coding!
:)

Solution1 (by Aarti) is very good. Another way is to use CTE[^].
Try this:

DECLARE @tbl TABLE (id INT, member_photo1 NVARCHAR(30), member_photo2 NVARCHAR(30), member_photo3 NVARCHAR(30))

INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(223, 'photo1.jpg', 'photo2.jpg', 'photo3.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(224, 'photo10.jpg', 'photo20.jpg', 'photo30.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(225, 'photo100.jpg', 'photo200.jpg', 'photo300.jpg')
INSERT INTO @tbl (id, member_photo1, member_photo2, member_photo3)
VALUES(226, 'photo1000.jpg', 'photo2000.jpg', 'photo3000.jpg')

SELECT *
FROM @tbl

;WITH MyMembers AS
(
	SELECT id, member_photo1 AS PhotoName
	FROM @tbl
	UNION ALL
	SELECT id, member_photo2 AS PhotoName
	FROM @tbl
	UNION ALL
	SELECT id, member_photo3 AS PhotoName
	FROM @tbl
)
SELECT * 
FROM MyMembers
ORDER BY id

Result:

223	photo1.jpg
223	photo2.jpg
223	photo3.jpg
224	photo30.jpg
224	photo20.jpg
224	photo10.jpg
225	photo100.jpg
225	photo200.jpg
225	photo300.jpg
226	photo3000.jpg
226	photo2000.jpg
226	photo1000.jpg

这篇关于如何将列转换为具有相同主键ID的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！