converting rows to columns

1. use a CASE statement

SELECT DATEPART(YEAR,orderdate),
    SUM(CASE WHEN DATEPART(q,orderdate) = 1 THEN orderamount ELSE 0 END) AS Qtr1,
    SUM(CASE WHEN DATEPART(q,orderdate) = 2 THEN orderamount ELSE 0 END) AS Qtr2,
    SUM(CASE WHEN DATEPART(q,orderdate) = 3 THEN orderamount ELSE 0 END) AS Qtr3,
    SUM(CASE WHEN DATEPART(q,orderdate) = 4 THEN orderamount ELSE 0 END) AS Qtr4,
    SUM(orderamount) AS Total
 FROM Orders
-- additional where clause goes here...
GROUP BY DATEPART(YEAR,orderdate)

2. use the COALESCE function

DECLARE @AllValues VARCHAR(4000)

SELECT @AllValues = COALESCE(@AllValues + ',', '') + HandlingCode
FROM OrdersDetails
WHERE OrderNumber = @OrderNumber

3. use ROW_NUMBER() OVER (ORDER BY)

SELECT OrderNumber, OrderDate,
--get the special handling codes and show them as columns, max of 3 (agreed by users)
    (SELECT HandlingCode
        FROM
        (
          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
          FROM OrdersDetails
          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
        ) HandlingCode
        WHERE ROWNUMBER = 1) HandlingCode1,
    (SELECT HandlingCode
        FROM
        (
          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
          FROM OrdersDetails
          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
        ) HandlingCode
        WHERE ROWNUMBER = 2) HandlingCode2,
    (SELECT HandlingCode
        FROM
        (
          SELECT ROW_NUMBER() OVER (ORDER BY HandlingCode) AS ROWNUMBER,HandlingCode
          FROM OrdersDetails
          WHERE OrdersDetails.OrderNumber = Orders.OrderNumber
        ) HandlingCode
        WHERE ROWNUMBER = 3) HandlingCode3
FROM Orders
WHERE OrderNumber = @OrderNumber

4. Use an inline function

SELECT OrderNumber, OrderDate, fn_GetHandlingCodes (OrderNumber)
FROM Orders
WHERE OrderNumber = @OrderNumber

CREATE FUNCTION dbo.fn_GetHandlingCodes (@OrderNumber INT)
RETURNS VARCHAR(1200)
AS
BEGIN
   DECLARE @HandlingCode VARCHAR(20)
   DECLARE @ReturnValue  VARCHAR(4000)

-- use that fastest cursor methods: local fast_forward
   DECLARE code_cursor CURSOR LOCAL fast_forward FOR
    SELECT HandlingCode
     FROM OrdersDetails
    WHERE OrderNumber = @OrderNumber  
      AND NOT HandlingCode IS NULL -- filled

   SET @ReturnValue = ''  -- set to non null

   OPEN code_cursor
   FETCH NEXT FROM code_cursor  INTO @HandlingCode
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
       SET @ReturnValue = @ReturnValue + @HandlingCode + ', '      

       IF LEN (@ReturnValue) > 1000 BREAK -- avoid overflow

       FETCH NEXT FROM code_cursor INTO @HandlingCode
   END

   CLOSE code_cursor
   DEALLOCATE code_cursor

-- remove last delimiter
   IF LEN(@ReturnValue) > 1 SET @ReturnValue = SUBSTRING(@ReturnValue,1,LEN(@ReturnValue)-2)

   RETURN @ReturnValue
END

5. Use a pivot

USE AdventureWorks
GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

SQL Scripts Tips

@IS_INT = PATINDEX(‘%[^0-9]%’ ,@input)
@IS_ALPHA = PATINDEX(‘%[^A-Z]%’ ,@input)
@IsAlphaNumeric = PATINDEX('%[^a-Z,0-9,'' '']%', @input) > 0

@IsAlphaNumeric = NOT(@input LIKE '%[^a-Z,0-9,'' '']%')

How to get column names?

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Your Table Name'

ORDER BY ORDINAL_POSITION

How to create columns list from existing table?

select @col = stuff((select ','+quotename(C.COLUMN_NAME)
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = 'Table2'
                     order by C.ORDINAL_POSITION

                     for xml path(''), type).value('.',  'nvarchar(max)'), 1, 1, '');
set @sql = 'select '+@col+' into Table1 from Table2 union all '+

           'select '+@col+' from Table3';

Multi language in ASP.NET

Oracle tips

sqlplus username/password@connect_identifier spool output.log select * from users; spool off; --disable escape parameter SET DEFINE OFF Check language setting for Oracle select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; Oracle SQL Developer Tools for Visual Studio 2013 - MSI Installer http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html 1.Remeber to commit after execute the scripts

ETL Tools

Fully Automated ETL Testing: a Step by Step Guide

Performance of bulk operations

1. Efficient way to bulk load data in .NET using SqlBulkCopy: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx This SqlBulkCopy outperforms the ADO.NET SqlDataAdapter bulk operations a lot. Check out the performance analysis in this link: http://sqlblog.com/blogs/alberto_ferrari/archive/2009/11/30/sqlbulkcopy-performance-analysis.aspx looks very impressive. 2. Bulk Insert using SSIS: http://henkvandervalk.com/speeding-up-ssis-bulk-inserts-into-sql-server (impressive 22GB in over 8 minutes) 3. Data Loading Performance Guide: http://msdn.microsoft.com/en-us/library/dd425070.aspx