Comprehensive SQL Techniques: Pivot/Unpivot, String Replacement, Date Conversion, Division, NULL Handling, and Performance Tips

1. Row‑to‑Column Pivot

<code>CREATE table test (id int, name nvarchar(20), quarter int, number int);
INSERT into test values (1,N'苹果',1,1000);
INSERT into test values (1,N'苹果',2,2000);
INSERT into test values (1,N'苹果',3,4000);
INSERT into test values (1,N'苹果',4,5000);
INSERT into test values (2,N'梨子',1,3000);
INSERT into test values (2,N'梨子',2,3500);
INSERT into test values (2,N'梨子',3,4200);
INSERT into test values (2,N'梨子',4,5500);
SELECT * FROM test;</code>

Result: (see image)

<code>SELECT ID, NAME, [1] as '一季度', [2] as '二季度', [3] as '三季度', [4] as '四季度'
FROM test
PIVOT (SUM(number) FOR quarter IN ([1],[2],[3],[4])) as pvt;</code>

Result: (see image)

2. Column‑to‑Row Unpivot

<code>CREATE table test2 (id int, name nvarchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
INSERT into test2 values (1,N'苹果',1000,2000,4000,5000);
INSERT into test2 values (2,N'梨子',3000,3500,4200,5500);
SELECT * FROM test2;</code>

Result: (see image)

<code>SELECT id, name, quarter, number
FROM test2
UNPIVOT (number FOR quarter IN (Q1, Q2, Q3, Q4)) as unpvt;</code>

Result: (see image)

3. String Replacement with REPLACE and SUBSTRING

<code>SELECT REPLACE('abcdefg', SUBSTRING('abcdefg',2,4), '**');
SELECT REPLACE('13512345678', SUBSTRING('13512345678',4,11), '********');
SELECT REPLACE('[email protected]', '1234567', '******');</code>

Results: (see images)

4. Finding Duplicate Records Using HAVING

<code>SELECT * FROM HR.Employees
WHERE title IN (
    SELECT title FROM HR.Employees
    GROUP BY title
    HAVING COUNT(1) > 1
);</code>

Result: (see image)

Alternative using concatenated fields:

<code>SELECT * FROM HR.Employees
WHERE title+titleofcourtesy IN (
    SELECT title+titleofcourtesy FROM HR.Employees
    GROUP BY title, titleofcourtesy
    HAVING COUNT(1) > 1
);</code>

Result: (see image)

5. Adding New Columns via Conditional Aggregation

<code>SELECT id, name,
    SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) as '一季度',
    SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) as '二季度',
    SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) as '三季度',
    SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) as '四季度'
FROM test
GROUP BY id, name;</code>

Result: (see image)

6. Table Copy Methods

<code>-- Method 1
INSERT INTO table2 (field1, field2, ...) VALUES (value1, value2, ...);

-- Method 2
INSERT INTO Table2 (field1, field2, ...) SELECT value1, value2, ... FROM Table1;

-- Method 3
SELECT value1, value2 INTO Table2 FROM Table1;</code>

7. Updating with Correlated Subqueries

<code>-- Method 1
UPDATE Table1 SET c = (SELECT c FROM Table2 WHERE a = Table1.a) WHERE c IS NULL;

-- Method 2
UPDATE A SET newquantity = B.quantity FROM A, B WHERE A.bnum = B.bnum;

-- Method 3
UPDATE (
    SELECT A.bnum, A.newquantity, B.quantity FROM A LEFT JOIN B ON A.bnum = B.bnum
) AS C SET C.newquantity = C.quantity WHERE C.bnum = '001';</code>

8. Remote Server Query via OPENROWSET

<code>SELECT * FROM openrowset('SQLOLEDB','server=192.168.0.1;uid=sa;pwd=password','SELECT * FROM dbo.test');</code>

9. Date and Time Formatting with CONVERT

<code>SELECT CONVERT(varchar(100), GETDATE(), 0);   -- mon dd yyyy hh:miAM/PM
SELECT CONVERT(varchar(100), GETDATE(), 101); -- mm/dd/yy
SELECT CONVERT(varchar(100), GETDATE(), 102); -- yy.mm.dd
... (styles 103‑126 shown in table)</code>

10. Division with Percentage Formatting

<code>SELECT CASE WHEN ISNULL(A-B,0)=0 THEN ''
    ELSE CAST(CONVERT(DECIMAL(18,2), A*100.0/(A-B)) AS VARCHAR(10)) + '%'
END AS '百分数'
FROM YourTable;</code>

Alternative using ROUND:

<code>SELECT (CONVERT(VARCHAR(20), ROUND(41*100.0/88,3)) + '%') AS '百分比';</code>

11. ROUND Function for Rounding and Truncating

<code>SELECT ROUND(150.45648,2);          -- 150.46000 (round)
SELECT ROUND(150.45648,2,0);      -- 150.46000 (round, default)
SELECT ROUND(150.45648,2,1);      -- 150.45000 (truncate)
SELECT ROUND(150.45648,2,2);      -- 150.45000 (truncate)</code>

12. Handling NULL Values

<code>SELECT CASE WHEN [字段名] IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(20), [字段名]) END AS NewName;

SELECT COALESCE([字符串字段], 'N') AS NewName;
SELECT COALESCE(CONVERT(VARCHAR(20), [非字符串字段]), 'N') AS NewName;
SELECT COALESCE(NULL,NULL,1,2,NULL); -- returns 1</code>

13. COUNT Variations

<code>SELECT COUNT(*) FROM tablename;
SELECT COUNT(ID) FROM tablename;
SELECT COUNT(1) FROM tablename;</code>

14. UNION ALL for Multi‑Table Insert

<code>SELECT * INTO table_new FROM table_1 UNION ALL SELECT * FROM table_2;</code>

15. Viewing Cached SQL Statements

<code>USE master;
DECLARE @dbid INT;
SELECT @dbid = dbid FROM sysdatabases WHERE name = 'SQL_ROAD';
SELECT dbid, UseCounts, RefCounts, CacheObjtype, ObjType, DB_Name(dbid) AS DatabaseName, [SQL]
FROM syscacheobjects
WHERE dbid = @dbid
ORDER BY dbid, UseCounts DESC, ObjType;</code>

16. Clearing Plan Cache

<code>-- Clear entire cache
DBCC FREEPROCCACHE;

-- Clear cache for a specific database
USE master;
DECLARE @dbid INT;
SELECT @dbid = dbid FROM sysdatabases WHERE name = 'SQL_ROAD';
DBCC FLUSHPROCINDB(@dbid);
</code>

17. Adding Line Breaks in SQL Output

<code>PRINT 'SQL' + CHAR(13) + 'ROAD';
PRINT 'SQL' + CHAR(10) + 'ROAD';
PRINT 'SQL' + CHAR(9) + 'ROAD';</code>

When results are displayed as text, the CHAR function inserts the desired line‑break characters.

18. TRUNCATE vs DELETE

<code>TRUNCATE TABLE dbo.YourTable;   -- Fast, minimal logging, resets identity
DELETE FROM dbo.YourTable;    -- Row‑by‑row logging, preserves identity</code>

TRUNCATE cannot be used when the table is referenced by a FOREIGN KEY.

19. Common System Diagnostic Scripts

<code>DBCC MEMORYSTATUS;          -- View memory usage
EXEC sp_who active;        -- Identify blocking sessions
EXEC sp_lock;              -- Show locked resources</code>

20. Measuring Script Execution Time

<code>DECLARE @timediff DATETIME;
SELECT @timediff = GETDATE();
SELECT * FROM Suppliers;
PRINT '耗时:' + CONVERT(VARCHAR(10), DATEDIFF(ms, @timediff, GETDATE()));
</code>

The printed output shows the elapsed time in milliseconds.