For Path

/****** Script for SelectTopNRows command from SSMS  ******/


DECLARE @table TABLE (姓名 VARCHAR(10),课程 VARCHAR(10),分数 INT)
 
insert into @table VALUES ('张三','语文',74)
insert into @table VALUES ('张三','数学',83)
insert into @table VALUES ('张三','物理',93)
insert into @table VALUES ('李四','语文',74)
insert into @table VALUES ('李四','数学',84)
insert into @table VALUES ('李四','物理',94)
 
 
SELECT * FROM @table
 
SELECT *  FROM @table PIVOT( MAX(分数) FOR 课程 IN (语文,数学,物理))a
 
 
DECLARE @table1 TABLE (姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)
 
INSERT INTO @table1 VALUES('张三',74,83,93)
 
INSERT INTO @table1 VALUES('李四',74,84,94)
 
SELECT * FROM @table1
SELECT  姓名 ,
        课程1 ,
        分数1
FROM    @table1 UNPIVOT ( 分数1 FOR 课程1 IN ( [语文], [数学], [物理] ) ) t;


SELECT TOP 1000
        [id],[GroupId],[CvNumber],[Name],[PFId],[JoinId],[Flag],[flagName],[identityName],[title]
FROM    [Ms_Mobile].[dbo].[V_UserIdentity] WHERE CvNumber=131285



    SELECT A.GroupId, A.CvNumber,(
	        
			 SELECT
			    CAST(Flag AS VARCHAR(12))+':'+identityName+',' 
			 FROM  V_UserIdentity
			 WHERE CvNumber=A.CvNumber AND GroupId=a.GroupId
			 FOR  XML PATH('')
	    ) AS C
	FROM V_UserIdentity AS A
	WHERE A.PFId=1
	GROUP BY A.CvNumber,A.GroupId;

	WITH ct1 AS (
	SELECT B.GroupId,B.CvNumber,LEFT(B.C,LEN(B.C)-1) AS flag FROM (
	    SELECT A.GroupId, A.CvNumber,(
			 SELECT
			    CAST(Flag AS VARCHAR(12))+',' 
			 FROM  V_UserIdentity
			 WHERE CvNumber=A.CvNumber AND GroupId=a.GroupId
			 FOR  XML PATH('')
	    ) AS C
	FROM V_UserIdentity AS A
	WHERE A.PFId=1
	GROUP BY A.CvNumber,A.GroupId
	) AS B 
	) 
	SELECT  * FROM ct1  WHERE ct1.CvNumber=131285



	----2009级 在职  人力资源管理,
	----人资系 副讲师
	--position 4  人资+position
	
	


DECLARE @table3  TABLE(GroupId INT,CvNumber BIGINT,Name VARCHAR(50),JoinId INT,flagName VARCHAR(1024));
INSERT INTO @table3 (GroupId,CvNumber,Name,JoinId,flagName)
SELECT GroupId,CvNumber,Name,JoinId,flagName  FROM [Ms_Mobile].[dbo].[V_UserIdentity]  WHERE PFId=1 AND GroupId<>0

SELECT * FROM  @table3


 /*
 
SELECT  B.sName,LEFT(StuList,LEN(StuList)-1) as hobby
FROM    (SELECT sName,(
                       SELECT   hobby+','
                       FROM     student
                       WHERE    sName=A.sName
                      FOR  XML PATH('')
                ) AS StuList
         FROM   student A
         GROUP BY sName
        ) B 
 
 */


 SELECT  ROW_NUMBER()OVER(PARTITION BY CvNumber,GroupId ORDER BY Flag),
        [id],[GroupId],[CvNumber],[Name],[PFId],[JoinId],[Flag],[flagName],[identityName],[title]
FROM    [Ms_Mobile].[dbo].[V_UserIdentity] WHERE CvNumber=131285

　　

转载于:https://www.cnblogs.com/alphafly/p/5076290.html