Convert rows to column using pivot

DECLARE @t AS TABLE (key1 varchar(25), [Soucecolumn Name] varchar(25), ColumnValue varchar(25)) 
INSERT INTO @t VALUES 
('8308862', 'Name', '1a234'),
('8308862', 'GrossTons', '1a235'),
('8308862', 'CallSign', '1a236'),
('8308862', 'YearofBuild','1a237'),
('8308862' ,'Type','1a238'),
('8308862','Date','1a239'),
('8308862', 'NetTons','1a245'),
('8308862','OfficialNumber','1a247'),
('8308862','PlaceofBuild','1a278'), 
 --For other key
('8308863', 'Name', '1a234'),
('8308863', 'GrossTons', '1a235'),
('8308863', 'CallSign', '1a236'),
('8308863', 'YearofBuild','1a237'),
('8308863' ,'Type','1a238'),
('8308863','Date','1a239'),
('8308863', 'NetTons','1a245'),
('8308863','OfficialNumber','1a247'),
('8308863','PlaceofBuild','1a278')

SELECT key1, [Name], [GrossTons] ,[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild]
FROM @t 
PIVOT (MAX(ColumnValue) FOR [Soucecolumn Name] IN ([Name], [GrossTons],[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild])) AS pvt
Advertisements

TSQL Cursor Syntext

declare cursor cust_cursor fast_forward for
select custid from tblCustomer
Open cust_cursor
fetch next from cust_cursor into @custId
while @@fetch_status = 0
begin
fetch next from cust_cursor into @custId
end
close cust_cursor
deallocate cust_cursor

Difference between Date Datatypes in SQL Server 2012

What is basic difference between different date data types in sql server 2012. In SQL 2012, there are new date datatypes have been included such as Datetime2, DateTimeOffset, smalldatetime, Time, Date.

Major difference between each is date time precision and storage space. So, Let look by changing one date data type to other how much space we could save. And saving more space that also means when we index, retrieve or filter data using where condition it also have performance benefits.

The best way to find out how much memory being using by datatype is to create table with datatypes and check the column description using SP_helptext [table_name].


create table tblDate (dt1 datetime, dt2 datetime2,
t1 time, d1 date, dtoff datetimeoffset, sdt smalldatetime)

 

 

2013-12-03_0311

Select Table name and press Alt+F1 or use sp_helptext tblDate.

It’s clear from the data filed length that if you want to store just date then use DATE field and if your looking to store time then go for SMALLDATETIME, for little more sophesticated datetime then just follow the conventional DATETIME.

But if you are looking for precision then it’s advise to use DATETIME2(7) use it with precision so that you are not over burdening the server to store more precise values.

DATETIMEOFFSET is the most expensive date datatype in sql 2012, having said that it might be more appropriate to use when your application is dealing with multiple time zones.

I also found this link which gives you max date value supported by sql server.

check it out

Import and Export data from csv in TSQL

There are common task which you have to perform as DBA or as programmer to import/export data in databbase.

it’s really easy to import/export data from SQL Server if you know the correct commands to use it.

here are easiest way which i found to import/export data from csv to table vise versa.

Use bulk insert statement in T-SQL


BULK INSERT [table_name]
FROM 'C:\Users\Public\data.csv'
WITH(FIELDTERMINATOR = ',', FIRSTROW=0, ROWTERMINATOR = '\n')

just make sure you put the CSV file in public folder or folder which have read permission from sql server account (like MS SQL server folder on program files).
otherwise you will always get an error that sql server can’t access or see the CSV file.

Use BCP utility using command line for export data into csv from table

bcp "SELECT TOP 3 * FROM test.dbo.table1" queryout c:\temp\table1.csv -c -t, -T -S .\SQLEXPRESS

Group by concat string of Column value in TSQL

how to get following query by using just single statement

id       Name       Value
1          A          4
1          B          8
2          C          9

to

id          Column
1          A:4, B:8
2          C:9

and query is 

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH (''))
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

TSQL function return string with Capital initial character

CREATE FUNCTION [dbo].[fnInitCap] ( @InputString varchar(max) ) 
RETURNS VARCHAR(max)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(max)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END

Select one row from detail records based on condition.

I often come across the issue to return one row from detail table like invoice items or order details for one customer for specific condition.
i.e. show me the max order product for each customer and order

that kind of query could be solved using self join, grouping and having clause

we have three tables, 1. customer 2. Order 3. OrderItem

the query look like

select c.customer_id, oi.order_id, oi.product_cost from order_item oi 
inner join order_item oii on oi.order_id = oii.order_id
inner join customer c on c.customer_id = oi.customer_id
group by c.customer_id, oi.order_id, oi.product_cost
having oi.product_cost >= max(oii.product_cost)