MSSQL Cross-Tab Query

Example Script for MSSQL Cross-Tab Query

Create table #Table
(
yearofJoining int,
EmpId int,
Deptid int
)
GO


insert into #Table select 1990,1,1
insert into #Table select 1991,2,2
insert into #Table select 1990,3,4
insert into #Table select 1991,4,2
insert into #Table select 1990,5,1
insert into #Table select 1990,6,3
insert into #Table select 1992,7,3
insert into #Table select 1990,8,4
insert into #Table select 1993,9,1
insert into #Table select 1994,10,2
insert into #Table select 1990,11,3
insert into #Table select 1995,12,3
insert into #Table select 1995,14,3
insert into #Table select 1995,15,3
insert into #Table select 1995,16,6
GO

--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #Table where deptid in(1,2,3)
group by Yearofjoining

Out put:

YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990 2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3

PIVOT Example:


SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #Table) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining

Table Row in String

How to create the row data as string.

If you have table row data and you need to show all the records in a single string, use the following query.

DATA:
--------------------
Name
RAM
SHYAM
HARI
RITA

RESULT:
---------------------
Name
RAM,SHYAM,HARI,RITA

Use the following query
------------------------------------
DECLARE @strList varchar(100)
SELECT @strList = COALESCE(@strList + ', ', '') +
 CAST(Name AS varchar(5))
FROM TABLE1

SELECT @strList

There are complex example in internet but you can use this simple query to get this solutions. No need to use complex cursor to get such result.

How to parametrize OPENQUERY in MSSQL

How to parametrize OPENQUERY in MSSQL:

MSSQL has good feature of linked server between two different Database in same location or in remote location.
OPENQUERY helps you to execute your query in Linked server (remote) .

In linked server you can directly execute your query without OPENQUERY but the performance will be a issue.

As per my experience OPENQUERY is best way to access the linked servers.

If you want to pass your variable in OPENQUERY than it is so simple. Use the following way to pass your variable in OPENQUERY.


DECLARE @strDate VARCHAR(10)
DECLARE @strSql VARCHAR(8000)
DECLARE @strLinked VARCHAR(8000)

SET @strDate= '2009-01-01'

SET @strSsql= 'SELECT * FROM TABLE1 WHERE TRN_DATE =' + '''' + '''' + @strDate+ '''' + ''''

SET @strLinked= 'SELECT * FROM OPENQUERY(MYLINKEDSERVER,' + '''' + @strSql+ '''' + ')'

--############ Print and see your script
PRINT @strLinked

--############ Final Execution
EXEC(@strLinked)

As per MSDN:

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Find All SQL Server Instance Running in Local Network

How to list all SQL Server Instance Running in Local Network:
Some time you need to see the List of other running SQL server on your network. It is so simple and easy to call from other application also.

See the example:

-- ######### Query
EXEC master..xp_cmdshell 'osql -L'

-- ############ Result

NULL
Servers:
    (local)
    MAIL_SERVER
    RAGHU
    RAGHU\SQLEXPRESS
    SERVER1
NULL

If you want to get the same list from Command prompt then:

C:\> osql -L

it will show you the same list.