Category: sql

float to nvarchar in sql server

Posted on by tekweirdo in Microsoft SQL Server, sql

when you need to select from one column of datatype float(which has phone number) and insert it in another column as nvarchar- cast wont work

cast : cast([Column_Name] as nvarchar(50)). The result will be in format 9.07235e+009 instead of a 10 digit number (phone number).

use SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs

Recursive SQL query to find the parent record or first and last or latest records

Posted on by tekweirdo in sql

When we have a scenario to pick up first and  last record/hierarchy from a table which maintains history of changes(EmployeeHistory_Table).

 

Hierarchy_Table

role hierarchies
Trainee_System_Engineer
System_Engineer
Senior_Software_Eng
Team_Leader
Manager
Senior_Exec

 

EmployeeHistory_Table

emp_name current_role new_role
Dan Trainee_System_Engineer System_Engineer
Dan System_Engineer Senior_Software_Eng
Dan Senior_Software_Eng Manager
Tom System_Engineer Senior_Software_Eng
Tom Senior_Software_Eng Team_Leader
Jack System_Engineer Team_Leader
Jack Team_Leader Manager
Jack Manager Senior_Exec

 

expected output:

emp_name started_role current_role
Jack System_Engineer Trainee_System_Engineer
Tom System_Engineer Team_Leader
Dan Trainee_System_Engineer Manager

 

below is the sql for this kind of scenario:

CREATE TABLE #emp_RoleChanges (
emp_name VARCHAR(30),
current_role VARCHAR(30) ,
new_role VARCHAR(30)
);

INSERT INTO #emp_RoleChanges (emp_name , current_role , new_role )
VALUES
(‘Dan’, ‘Trainee_System_Engineer’, ‘System_Engineer’),
(‘Dan’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Dan’, ‘Senior_Software_Eng’, ‘Manager’),
(‘Tom’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Tom’, ‘Senior_Software_Eng’, ‘Team_Leader’),
(‘Jack’, ‘System_Engineer’, ‘Team_Leader’),
(‘Jack’, ‘Team_Leader’, ‘Manager’),
(‘Jack’, ‘Manager’, ‘Trainee_System_Engineer’)
;

select * from #emp_RoleChanges
;
WITH q AS (
SELECT 1 AS LEVEL, emp_name , current_role , new_role
FROM #emp_RoleChanges
UNION ALL
SELECT LEVEL + 1, q.emp_name, q.current_role, u.new_role
FROM q
INNER JOIN #emp_RoleChanges u ON u.current_role = q.new_role and u.emp_name = q.emp_name
)
–select * from q
–order by 2,1

SELECT –q.LEVEL,
q.emp_name , q.current_role started_role, q.new_role current_role
FROM q
where q.level = (select max(p.level) from q p where p.emp_name = q.emp_name)

replace char(13) char(10)

Posted on by tekweirdo in Microsoft SQL Server, sql

char(13) is carriage return and char(10) is line feed. Different text editors/viewers will interpret it differently.

Use replace function to get all data in 1 line.
replace(replace(replace(column_name,char(13),’ ‘),char(10),’ ‘), char(9), ‘ ‘)

when copying data which have char(13), char(10) in it to text editor/excel- we notice data showing up in different rows. For this scenario we replace it by ‘ ‘ to get in 1 row.

select ‘a’+char(13)+’b’
select ‘a’+char(10)+’b’

SQL Carriage Returns or Tabs in SQL Server strings

Char(10) – New Line / Line Break

Char(13) – Carriage Return

Char(9) – Tab

Convert multiple rows to single column

Posted on by tekweirdo in sql

To convert multiple rows to single column.

 

At SQL Server database this can be achieved using STUFF function.

The STUFF function inserts a string into another string.

 

Run below queries to get an idea

 

—–Query Start————

create table #temp
(
personid int,
name varchar(10),
subjects varchar(10)
)
;
insert into #temp values(1, ‘mike’, ‘maths’)
insert into #temp values(1, ‘mike’, ‘science’)
insert into #temp values(1, ‘mike’, ‘social’)
insert into #temp values(2, ‘jay’, ‘economics’)
insert into #temp values(2, ‘jay’, ‘history’)
insert into #temp values(3, ‘joe’, ‘maths’)
insert into #temp values(4, ‘kelly’, ‘geography’)
insert into #temp values(4, ‘kelly’, ‘computers’)
insert into #temp values(5, ‘mike’, ‘literature’)
;
select * from #temp

stuff input
;
SELECT personid, name,
subjects = STUFF(
(SELECT ‘, ‘ + b1.subjects FROM  #temp b1 where b1.personid = b2.personid and b1.name = b2.name FOR XML PATH (”)), 1, 1, ”
)
FROM #temp b2   GROUP BY b2.personid, name

stuff output

 

create table #temp
(
personid int,
name varchar(10),
subjects varchar(10)
)
;
insert into #temp values(1, 'mike', 'maths')
insert into #temp values(1, 'mike', 'science')
insert into #temp values(1, 'mike', 'social')
insert into #temp values(2, 'jay', 'economics')
insert into #temp values(2, 'jay', 'history')
insert into #temp values(3, 'joe', 'maths')
insert into #temp values(4, 'kelly', 'geography')
insert into #temp values(4, 'kelly', 'computers')
insert into #temp values(5, 'mike', 'literature')
;
select * from #temp

;
SELECT personid, name,
subjects = STUFF(
(SELECT ', ' + b1.subjects FROM #temp b1 where b1.personid = b2.personid and b1.name = b2.name FOR XML PATH ('')), 1, 1,''
)
FROM #temp b2 GROUP BY b2.personid, name

reference:

https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server