SQL 進階必備：六大經典 T-SQL 技巧彙總

1. 動態行轉列

DECLARE @sql VARCHAR(4000);
SET @sql = 'SELECT Name';
SELECT @sql = @sql + ',SUM(CASE Subject WHEN ''' + Subject + ''' THEN Result END) [' + Subject + ']'
FROM (SELECT DISTINCT Subject FROM CJ) AS A;
SET @sql = @sql + ' FROM CJ GROUP BY Name';
EXEC(@sql);

2. 群組字串串連

CREATE FUNCTION fMerge(@Id INT) RETURNS VARCHAR(8000) AS
BEGIN
    DECLARE @Str VARCHAR(8000); SET @Str = '';
    SELECT @Str = @Str + ',' + CAST(Pid AS VARCHAR) FROM TableA WHERE Id = @Id;
    SET @Str = RIGHT(@Str, LEN(@Str)-1);
    RETURN @Str;
END
GO
SELECT DISTINCT Id, dbo.fMerge(Id) FROM TableA;

3. 取得資料表所有欄位名

DECLARE @ObjId INT, @ObjName CHAR(40);
SET @ObjName = 'TableName';
SELECT @ObjId = Id FROM SysObjects WHERE Id = OBJECT_ID(@ObjName);
SELECT 'Column_Name' = Name FROM SysColumns WHERE Id = @ObjId ORDER BY ColId;

4. 修改使用者密碼

EXEC sp_password NULL, 'NewPass', 'UserName';  -- 需 sysadmin
EXEC sp_password NULL, 'NewPass', 'sa';        -- 修改 sa

5. 查詢非空欄位

SELECT Column_Name FROM Information_Schema.Columns
WHERE Is_Nullable = 'NO' AND Table_Name = 'TableName';

6. 尋找擁有相同欄位名的資料表

-- 已知欄位名
SELECT b.Name AS TableName, a.Name AS ColumnName
FROM SysColumns a JOIN SysObjects b ON a.Id = b.Id
WHERE b.XType = 'U' AND a.Name = 'TargetColumn';

-- 未知欄位名：找出現於多張表的欄位
SELECT o.Name AS TableName, c.Name AS ColumnName, COUNT(*) OVER (PARTITION BY c.Name) AS Cnt
FROM SysColumns c JOIN SysObjects o ON c.Id = o.Id
WHERE o.XType = 'U'
GROUP BY o.Name, c.Name
HAVING COUNT(*) OVER (PARTITION BY c.Name) > 1;