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
Tag: tsql
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)
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.
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 9to
id Column
1 A:4, B:8
2 C:9and 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)
Just another tech blog 