Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query help

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2015-04-30 : 17:48:21
I have table with many records and need a query to get expected results with best performance..



studentID studentCode studentFees
--------- --------- -----------
100 04 10
101 04 5
102 04 10
103 05 10
104 05 10


Output:
studentID student04Code student04Fees student05Fees student05Code
--------- --------- ----------- ------------- ------------
100 04 10 NULL NULL
101 04 5 NULL NULL
102 04 10 NULL NULL
103 Null Null 10 05
104 Null Null 10 05


Thanks for your help in advance

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-05-01 : 01:46:33
StudentCode is only 04 and 05 ?
If yes then:

SELECT
S.studentID
,CASE WHEN studentCode ='04' THEN studentCode ELSE NULL END AS student04Code
,CASE WHEN studentCode ='04' THEN studentFees ELSE NULL END AS student04Fees
,CASE WHEN studentCode ='05' THEN studentCode ELSE NULL END AS student05Code
,CASE WHEN studentCode ='05' THEN studentFees ELSE NULL END AS student05Fees
FROM @tSample AS S





sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-05-01 : 02:16:50
with dynamic values for StudentCode

DECLARE @columns NVARCHAR(MAX)
,@columnsFees NVARCHAR(MAX)
,@columnsAntet NVARCHAR(MAX)
,@sql NVARCHAR(MAX);
SET @columns = N'';
SET @columnsFees =N''
SET @columnsAntet = N'';


SELECT @columns += N', ' + QUOTENAME(Name)
FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S
GROUP BY S.studentCode) AS x;
SELECT @columnsFees += N', ' + QUOTENAME('F'+Name)
FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S
GROUP BY S.studentCode) AS x;

SELECT @columnsAntet += N', ' + QUOTENAME(Name) + 'AS student'+Name+'Code, '
+ QUOTENAME('F'+Name) + 'AS student'+Name+'Fees'
FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S
GROUP BY S.studentCode) AS x;

SET @sql = N'
SELECT studentID ,' + STUFF(@columnsAntet, 1, 2, '') + '
FROM
(
SELECT studentID,studentCode, ''F''+studentCode AS studentCodeFees , studentFees FROM #tmpSample
) AS j
PIVOT
(
MAX(studentFees) FOR studentCodeFees IN ('
+ STUFF(REPLACE(@columnsFees, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
PIVOT
(
MAX(studentCode) FOR studentCode IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS R;';
PRINT @sql;
EXEC sp_executesql @sql;


result set

studentID student04Code student04Fees student05Code student05Fees
100 04 10 NULL NULL
101 04 5 NULL NULL
102 04 10 NULL NULL
103 NULL NULL 05 10
104 NULL NULL 05 10




sabinWeb MCP
Go to Top of Page
   

- Advertisement -