Hybrid SQL

Hybrid SQL: - Hybrid Sql is nothing but it is a combination of TSql statements and MDX.

To create hybrid sql. We need to create a linked Server

Linked Server:-  linked server  enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked server

How to create a linked server:-

1.       To create a linked sever you have admin right of SqlServer.

2.       Navigate to Server Objects > Linked Servers

3.       Right click on Linked Servers and select New Linked Server.

Let’s take a sample database AdventureWorksDW2008R2 and create a linked server

USE

AdventureWorksDW2008R2

GO

EXEC

sp_addlinkedserver

@server='Test',

@srvproduct='',

@provider='MSOLAP',

@datasrc='localhost',

@catalog='Adventure Works DW 2008R2'

4.       Once the linked server is set we need another function OPENQUERY

Open Query: 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.

Syntax: - OPENQUERY (linked_server,’query’)

Now you can write your Script

USE

AdventureWorksDW2008R2

GO

Declare @MDXExpression as Varchar(MAX)

Create Table  #temp(

                                Country varchar(Max),

                                Sale_Amount  float,

                                Product_cost float,

                                Tax_amount Float,

                                TProduct_Cost Float

                                )

Set  @MDXExpression =

'SELECT

NON EMPTY

      {

      [Measures].[Sales Amount]

      ,[Measures].[Standard Product Cost]

      ,[Measures].[Tax Amount]

      ,[Measures].[Total Product Cost]

      } ON 0,

NON EMPTY

      {

NonEmpty ( [Sales Territory].[Sales Territory Region].[Sales Territory Region])

      } ON 1

FROM

      [Adventure Works]';     

Exec ('SELECT * INTO ##TestTemp1 FROM OpenQuery(Test,''' + @MDXExpression + ''')')

Insert into  #temp SELECT * from       ##TestTemp1

Here we are joining MDX output with Tsql Table DimSalesTerritory and  Mdxoutput #temp

SELECT      SalesTerritoryGroup,  #temp.*

FROM         DimSalesTerritory inner join #temp   on #temp.[Country]= DimSalesTerritory.SalesTerritoryCountry

1.As you can see we have create linked server above with named test.

2. Now we have created variable @MDXExpression which we are using  keep the MDX query in it and pass it to the Targeted server through open query.

3. Now we will dump the output of MDX query in #testtemp

Exec ('SELECT * INTO ##TestTemp FROM OpenQuery(Test,''' + @MDXExpression + ''')')

4. Then we can join the Mdx output with TSQl Table  as we did above in last Select clause

Single comma separated row into multiple rows

1.Single comma separated row into multiple rows.
Lets create table
  Create table #temp (Country varchar(15), City varchar(50))
  Insert into #temp Values ('INDIA','Pune, New Delhi,Mumbai, Chandigarh')
  Insert into #temp Values ('USA','New York, New Jersey,Washington, California')
lets see the Out of this table below
#Output1:-
Country   City
INDIA     Pune, New Delhi,Mumbai, Chandigarh
USA        New York, New Jersey,Washington, California
Excecute this query
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
FROM  (SELECT Country,           CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Output2:-
Country    city
INDIA     Pune
INDIA     New Delhi
INDIA     Mumbai
INDIA     Chandigarh
USA         New York
USA         New Jersey
USA         Washington
USA          California
2.Multiple rows into a single comma separated row as #Output1

Now you put the output2 in another table  i.e #temp2
SELECT temp.Country,     
Split.temp.value('.', 'VARCHAR(20)') AS city
into #temp2
FROM  (SELECT Country, CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String       FROM  #temp) AS temp  CROSS APPLY String.nodes ('/M') AS Split(temp);

Now Select This Query

select distinct  t1.Country, (
select t.City + ','
from #temp2 t
where t.Country=t1.Country
order by t.City
for xml path( '' )
)

from #temp2  t1