如何在sql中使用更改列进行排序 [英] how to use change columns to row in sql

问题描述

我目前的桌子是



id姓名电话地址

- ------------- -----------------

2 manoj 456 wes

3 senthil 789 east



我想要展示



id 2 3

------------ ---------------

名字manoj senthil

手机456 789

地址wes es



我在枢轴概念中尝试这个我正确的路线

解决方案

枢轴是一种转换方式数据。但首先，由于我不知道你将在哪里使用该解决方案，我建议在除SQL语句之外的其他地方制作所有格式，布局等。 SQL是一种专为数据检索和修改而设计的语言。通常情况下，最好使用专为此设计的工具（例如报告工具等）完成格式化。



话虽如此，请考虑以下示例

  CREATE   TABLE  t1 （
 id  int ，
 name  varchar （ 100 ），
手机 varchar （ 100 ），
地址 varchar （ 100 ）
）; 
 
  INSERT   INTO  t1  VALUES （ 2 ，'  manoj'，'  456'，'  wes'）; 
 
  INSERT   INTO  t1  VALUES （ 3 ，'  senthil'，'  789'，'  east'）; 
 
 
  SELECT  '  name'  as  subject，pvt。[ 2 ]，pvt。[ 3 ] 
  FROM （ SELECT  id ，名称
  FROM  t1）p 
 PIVOT（
 max（name）
  FOR  id  IN （[ 2 ]，[ 3 ]）
） AS  pvt 
  UNION   ALL  
  SELECT  ' < span class =code-string> phone'  as  subject，pvt。[ 2  ]，pvt。[ 3 ] 
  FROM （ SELECT  id，phone 
  FROM  t1）p 
 PIVOT（
 max（电话）
  FOR  id  IN （[  2 ]，[ 3 ]）
） AS  pvt 
  UNION  所有 
  SELECT  '  address'  as  subject，pvt。[ 2 ]，pvt。[ 3 ] 
  FROM （ SELECT  id，地址
  FROM  t1）p 
 PIVOT（
 max（地址）
  FOR  id  IN （[ 2 ]，[< span class =code-digit> 3 ]）
） AS  pvt 

这将导致

 subject 2 3 
 ------- ------ -  ------- 
名字manoj senthil 
电话456 789 
地址wes east 

Yup - pivot是正确的。



简短的答案永远不会感到完整，所以这里有一篇关于实施它们的文章：



在SQL查询中使用Pivot的简单方法 [ ^ ]

My current table is

id name phone address
-- ------------------------------
2 manoj 456 wes
3 senthil 789 east

I want to show

id 2 3
---------------------------
name manoj senthil
phone 456 789
address wes es

I am trying this in pivot concept am i in correct route

解决方案

Pivot is one way to transform the data. But first of all, since I don't know where you're going to use the solution I would advice to make all the formatting, layouts etc somewhere else than in an SQL statement. SQL is a language designed for data retrieval and modification. Typically it's best that the formatting is done with tools designed for that (such as reporting tools etc.)

Having that said, consider the following example

CREATE TABLE t1 (
   id int,
   name varchar(100),
   phone varchar(100),
   address varchar(100)
);

INSERT INTO t1 VALUES (2, 'manoj',  '456', 'wes');

INSERT INTO t1 VALUES (3, 'senthil', '789', 'east');


SELECT 'name' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, name
	  FROM t1) p
PIVOT (
	max (name)
	FOR id IN ([2], [3])
) AS pvt
UNION ALL
SELECT 'phone' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, phone
	  FROM t1) p
PIVOT (
	max (phone)
	FOR id IN ([2], [3])
) AS pvt
UNION ALL
SELECT 'address' as subject, pvt.[2], pvt.[3]
FROM (SELECT id, address
	  FROM t1) p
PIVOT (
	max (address)
	FOR id IN ([2], [3])
) AS pvt

This would result to

subject	2	3
------- ------- -------
name	manoj	senthil
phone	456	789
address	wes	east

Yup - pivot is correct.

Short answers never feel complete so here's an article on implementing them:

Simple Way To Use Pivot In SQL Query[^]

这篇关于如何在sql中使用更改列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！