将行值转换为列名SQL ser [英] Convert row values into column names SQL ser

问题描述

标题1标题2

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

Amikacin敏感度

氨苄青霉素抗性

头孢羟氨苄中间体

头孢哌酮抗性

氯霉素抗性

GENTAMYCIN敏感性

Netromycin灵敏度

利福霉素敏感度

替考拉宁抗性



我想转换以上结果如：



敏感度抗性中级

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

阿米卡星氨苄青霉素头孢羟氨苄
GENTAMYCIN头孢哌酮

Netromycin Chloramphenicol

利福霉素替考拉宁



我尝试过的：

Headings1 Headings2
--------------------------
Amikacin Sensitivity
Ampicillin Resistant
Cefadroxil Intermediate
Cefoperazone Resistant
Chloramphenicol Resistant
GENTAMYCIN Sensitivity
Netromycin Sensitivity
Rifamycin Sensitivity
Teicoplanin Resistant

I want to convert the above result like :

Sensitivity Resistant Intermediate
-------------------------------------------------------
Amikacin Ampicillin Cefadroxil
GENTAMYCIN Cefoperazone
Netromycin Chloramphenicol
Rifamycin Teicoplanin

What I have tried:

I've tried to query use pivot query but the value have to use aggregate function and the result is not appropriate. what should I do?

推荐答案

CREATE TABLE #thetable 
( id            INT IDENTITY(1,1) NOT NULL ,
headings1           VARCHAR(100),
headings2           VARCHAR(100),
)

INSERT INTO #thetable (headings1, headings2) VALUES ('Amikacin','Sensitivity')
INSERT INTO #thetable (headings1, headings2) VALUES ('Ampicillin','Resistant')
INSERT INTO #thetable (headings1, headings2) VALUES ('Cefadroxil','Intermediate')
INSERT INTO #thetable (headings1, headings2) VALUES ('Cefoperazone','Resistant')
INSERT INTO #thetable (headings1, headings2) VALUES ('Chloramphenicol','Resistant')
INSERT INTO #thetable (headings1, headings2) VALUES ('Netromycin','Sensitivity')

SELECT * FROM #thetable

-- this will work if you don't care about the NULL values and the position of the itmes
SELECT
  CASE WHEN headings2 = 'Sensitivity' THEN headings1 END AS 'Sensitivity'
  ,CASE WHEN headings2 = 'Resistant' THEN headings1 END AS 'Resistant'
  ,CASE WHEN headings2 = 'Intermediate' THEN headings1 END AS 'Intermediate'  
FROM  #thetable


-- this will have all NULL values at the bottom
SELECT
     t1.[Sensitivity]
	 ,t2.[Resistant]
	 ,t3.[Intermediate]
  FROM
( 
SELECT
  ROW_NUMBER() OVER (ORDER BY headings1) AS rn
  ,headings1 AS 'Sensitivity'
FROM  #thetable
WHERE headings2 = 'Sensitivity' 
) t1 FULL JOIN
(
SELECT
   ROW_NUMBER() OVER (ORDER BY headings1) AS rn
  ,headings1 AS  'Resistant'
FROM  #thetable
WHERE headings2 = 'Resistant' 
) t2 ON t1.rn = t2.rn  FULL JOIN
(
SELECT
   ROW_NUMBER() OVER (ORDER BY headings1) AS rn
  ,headings1 AS 'Intermediate'  
FROM  #thetable
WHERE headings2 = 'Intermediate' 
) t3 ON t1.rn = t3.rn

这篇关于将行值转换为列名SQL ser的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！