与数据选择有关的问题 [英] Question related to Data selection

问题描述

i选择一行数据总是

例如：

  SELECT  * 来自 tbl_test（表名称）其中​​ test_id =  1  

此查询给我一个结果如

 test_id名称地址移动
 1 TestName Baroda 6565656565 

但我想以枢轴方式选择数据

想要结果如

 ID CLM_NAME VALUE 
 1 test_id 1 
 2名称TestName 
 3地址Baroda 
 4 mobile [phone] 

请帮助我..谢谢

解决方案

试试这个：

  DECLARE   @ id   INT  
  SET   @ id  =  1  
 
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  T.RowID） AS  ID，CLM_NAME，[VALUE] 
  FROM （
  SELECT  test_id  AS  RowID，'   test_id'  AS  CLM_NAME， CONVERT （ NVARCHAR （ 10 ），test_id） AS  [VALUE] 
  FROM  tbl_test 
  UNION   ALL  
  SELECT  test_id  AS  RowID，'  name'  AS  CLM_NAME，[name]  AS  [VALUE] 
  FROM  tbl_test 
  UNION   ALL  
  SELECT  test_id  AS  RowID，'  address'  AS  CLM_NAME，地址 AS  [VALUE] 
  FROM  tbl_test 
  UNION   ALL  
  SELECT  test_id  AS  RowID，'  mobile'  AS  CLM_NAME， CONVERT （ NVARCHAR （ 30 ），mobile） AS  [VALUE] 
  FROM  tbl_test 
） AS  T 
  WHERE  T.RowID =  @ id  

Hi,

i select one row data always
example:

SELECT * from tbl_test(table name) where test_id = 1

this query give me a result like

test_id       name      address     mobile
1        TestName   Baroda     6565656565

but i want to select data in pivot manner
want result like

ID     CLM_NAME        VALUE
1       test_id          1
2       name           TestName
3       address        Baroda
4       mobile         [phone]

Please help me ..thanks

解决方案

Try this:

DECLARE @id INT
SET @id = 1

SELECT ROW_NUMBER() OVER(ORDER BY T.RowID) AS ID, CLM_NAME, [VALUE]
FROM (
    SELECT test_id AS RowID, 'test_id' AS CLM_NAME, CONVERT(NVARCHAR(10), test_id) AS [VALUE]
    FROM tbl_test
    UNION ALL
    SELECT test_id AS RowID, 'name' AS CLM_NAME, [name] AS [VALUE]
    FROM tbl_test
    UNION ALL
    SELECT test_id AS RowID, 'address' AS CLM_NAME, address AS [VALUE]
    FROM tbl_test
    UNION ALL
    SELECT test_id AS RowID, 'mobile' AS CLM_NAME, CONVERT(NVARCHAR(30), mobile) AS [VALUE]
    FROM tbl_test
) AS T
WHERE T.RowID = @id

这篇关于与数据选择有关的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！