如何将列值更改为行 [英] how to display the column value change into row

问题描述

列值在sql表中变成行或c急剧编码

怎么可能，

asap ....

column value is change into row in sql table or c sharp coding
how can it possible,
asap....

1.default table...
=================================
name      type       date
=================================
sekar     present    2015-06-01
sekar     present    2015-06-02
sekar     present    2015-06-03
sekar     absent     2015-06-04
sekar     present    2015-06-05
=================================

i want to like this below table.....
======================================================================
name     2015-06-01   2015-06-02   2015-06-03  2015-06-04  2015-06-05
=======================================================================
sekar     present      present      present    absent      present
========================================================================

推荐答案

HI，使用pivot元素，查看以下链接的详细信息，



提前感谢



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

use pivot element, see details from link below,

thanks in advance

Simple Way To Use Pivot In SQL Query[^]

--YOU COULD USED DYNAMIC PIVOT IF THE COLUMNS ARE DYNAMIC . HERE IS A SAMPLE 
CREATE TABLE #T
(
NAME VARCHAR(20),
STYPE VARCHAR(10),
SDATE DATETIME
)

INSERT INTO #T VALUES('ANIMESH' , 'PRESENT' , '2015-05-01')
INSERT INTO #T VALUES('ANIMESH' , 'PRESENT' , '2015-05-02')
INSERT INTO #T VALUES('ANIMESH' , 'ABSENT' , '2015-05-03')
INSERT INTO #T VALUES('ANIMESH' , 'PRESENT' , '2015-05-04')
  
 --VARIABLE FOR GET THE DATES IN SEQUENTIAL ORDER WITHIN A SINLE COLUMN
 DECLARE @COLS AS VARCHAR(MAX)
 
 --VARIABLE FOR DYNAMIC QUERY
 DECLARE @QUERY  AS NVARCHAR(MAX)


SELECT @COLS=ISNULL(@COLS+',','')+ '['+ CAST(CONVERT(DATE , T.SDATE) AS VARCHAR) + ']'  FROM #T T
SET @COLS= REVERSE(SUBSTRING(REVERSE(@COLS),1,LEN(@COLS)))
SET @QUERY = 'SELECT NAME, ' + @COLS  +' FROM 
            (
                SELECT *  FROM #T
           ) X
            PIVOT 
            (
                 MIN([STYPE])
                FOR [SDATE] IN (' + @COLS + ')
            ) P '

EXEC (@QUERY)

谢谢

Animesh

thanks
Animesh

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