pivot information: dynamic

Showing posts with label dynamic. Show all posts

Monday, March 26, 2012

Please Help

Hi,
I am creating a site on ASP and SQL, which would essentially be hosted in a
third party server like Interland or Net4India. It is dynamic in nature,
i.e. has its own database. But I have got a module in where I would need to
connect to an remote SQL server, which is located somewhere else. I know the
IP address. I would need to copy a table from that sql server to my server,
and synchronize it regularly, just in case something is updated.
Now my problem is,
(1) How do I connect to that remote sql server using ASP.
(2) Please suggest what method should i adopt to copy and synchronize the
data between the two servers.
Thank you very much,
Regards,
Bhaskardeep
Hi,
It seems you need to connect to a remote database over Internet, Am I
correct?
1. Assuming your SQL Server has a valid Internet address and sits outside
the firewall (very risky), this is easy. Just specify the server name and
address using Client Network Utility.
Server Alias: Server1
Address: 192.x.x.x or DNS
Port 1433
Note: This is using TCP/IP . I recommend looking into something like NAT for
more security.
Code will be some thing like;
Dim RMConn as ADODB.Connection
Private Sub Class_Initialize()
Set RMConn = New ADODB.Connection
With RMConn
.Provider = "SQLOLEDB"
.ConnectionString = "User ID=User;Data
Source=xxx.xxx.xxx.xxx;Initial Catalog=Database"
.Open
End With
End Sub
Have a look into the below site to setup the connection string;
http://www.connectionstrings.com/
2. Please suggest what method should i adopt to copy and synchronize the
data between the two servers
Method:1
Refer sp_addlinkedserver, sp_addlinkedserverlogin system procdures in
books online to syncronize the data.
Method:2
Incase if you need to populate 1 or 2 tables then preferably go for
1. Truncate the data
2. BCP OUT data from Remote server and BCP IN to Local server.
Thanks
Hari
MCDBA
"Bhaskardeep Khaund" <bhaskar_999@.hotmail.com> wrote in message
news:#jtMYuzHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating a site on ASP and SQL, which would essentially be hosted in
a
> third party server like Interland or Net4India. It is dynamic in nature,
> i.e. has its own database. But I have got a module in where I would need
to
> connect to an remote SQL server, which is located somewhere else. I know
the
> IP address. I would need to copy a table from that sql server to my
server,
> and synchronize it regularly, just in case something is updated.
> Now my problem is,
> (1) How do I connect to that remote sql server using ASP.
> (2) Please suggest what method should i adopt to copy and synchronize the
> data between the two servers.
> Thank you very much,
> Regards,
> Bhaskardeep
>

Friday, March 23, 2012

Please Help

Hi,
I am creating a site on ASP and SQL, which would essentially be hosted in a
third party server like Interland or Net4India. It is dynamic in nature,
i.e. has its own database. But I have got a module in where I would need to
connect to an remote SQL server, which is located somewhere else. I know the
IP address. I would need to copy a table from that sql server to my server,
and synchronize it regularly, just in case something is updated.
Now my problem is,
(1) How do I connect to that remote sql server using ASP.
(2) Please suggest what method should i adopt to copy and synchronize the
data between the two servers.
Thank you very much,
Regards,
BhaskardeepHi,
It seems you need to connect to a remote database over Internet, Am I
correct?
1. Assuming your SQL Server has a valid Internet address and sits outside
the firewall (very risky), this is easy. Just specify the server name and
address using Client Network Utility.
Server Alias: Server1
Address: 192.x.x.x or DNS
Port 1433
Note: This is using TCP/IP . I recommend looking into something like NAT for
more security.
Code will be some thing like;
Dim RMConn as ADODB.Connection
Private Sub Class_Initialize()
Set RMConn = New ADODB.Connection
With RMConn
.Provider = "SQLOLEDB"
.ConnectionString = "User ID=User;Data
Source=xxx.xxx.xxx.xxx;Initial Catalog=Database"
.Open
End With
End Sub
Have a look into the below site to setup the connection string;
http://www.connectionstrings.com/
2. Please suggest what method should i adopt to copy and synchronize the
data between the two servers
Method:1
Refer sp_addlinkedserver, sp_addlinkedserverlogin system procdures in
books online to syncronize the data.
Method:2
Incase if you need to populate 1 or 2 tables then preferably go for
1. Truncate the data
2. BCP OUT data from Remote server and BCP IN to Local server.
Thanks
Hari
MCDBA
"Bhaskardeep Khaund" <bhaskar_999@.hotmail.com> wrote in message
news:#jtMYuzHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating a site on ASP and SQL, which would essentially be hosted in
a
> third party server like Interland or Net4India. It is dynamic in nature,
> i.e. has its own database. But I have got a module in where I would need
to
> connect to an remote SQL server, which is located somewhere else. I know
the
> IP address. I would need to copy a table from that sql server to my
server,
> and synchronize it regularly, just in case something is updated.
> Now my problem is,
> (1) How do I connect to that remote sql server using ASP.
> (2) Please suggest what method should i adopt to copy and synchronize the
> data between the two servers.
> Thank you very much,
> Regards,
> Bhaskardeep
>sql

Please Help

Hi,
I am creating a site on ASP and SQL, which would essentially be hosted in a
third party server like Interland or Net4India. It is dynamic in nature,
i.e. has its own database. But I have got a module in where I would need to
connect to an remote SQL server, which is located somewhere else. I know the
IP address. I would need to copy a table from that sql server to my server,
and synchronize it regularly, just in case something is updated.
Now my problem is,
(1) How do I connect to that remote sql server using ASP.
(2) Please suggest what method should i adopt to copy and synchronize the
data between the two servers.
Thank you very much,
Regards,
BhaskardeepHi,
It seems you need to connect to a remote database over Internet, Am I
correct?
1. Assuming your SQL Server has a valid Internet address and sits outside
the firewall (very risky), this is easy. Just specify the server name and
address using Client Network Utility.
Server Alias: Server1
Address: 192.x.x.x or DNS
Port 1433
Note: This is using TCP/IP . I recommend looking into something like NAT for
more security.
Code will be some thing like;
Dim RMConn as ADODB.Connection
Private Sub Class_Initialize()
Set RMConn = New ADODB.Connection
With RMConn
.Provider = "SQLOLEDB"
.ConnectionString = "User ID=User;Data
Source=xxx.xxx.xxx.xxx;Initial Catalog=Database"
.Open
End With
End Sub
Have a look into the below site to setup the connection string;
http://www.connectionstrings.com/
2. Please suggest what method should i adopt to copy and synchronize the
data between the two servers
Method:1
Refer sp_addlinkedserver, sp_addlinkedserverlogin system procdures in
books online to syncronize the data.
Method:2
Incase if you need to populate 1 or 2 tables then preferably go for
1. Truncate the data
2. BCP OUT data from Remote server and BCP IN to Local server.
Thanks
Hari
MCDBA
"Bhaskardeep Khaund" <bhaskar_999@.hotmail.com> wrote in message
news:#jtMYuzHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am creating a site on ASP and SQL, which would essentially be hosted in
a
> third party server like Interland or Net4India. It is dynamic in nature,
> i.e. has its own database. But I have got a module in where I would need
to
> connect to an remote SQL server, which is located somewhere else. I know
the
> IP address. I would need to copy a table from that sql server to my
server,
> and synchronize it regularly, just in case something is updated.
> Now my problem is,
> (1) How do I connect to that remote sql server using ASP.
> (2) Please suggest what method should i adopt to copy and synchronize the
> data between the two servers.
> Thank you very much,
> Regards,
> Bhaskardeep
>

Tuesday, March 20, 2012

Planning on going with dynamic SQL, but...

I've read a few posts on the stored procedure vs dynamic sql debate. I ran a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored procs and just write the SQL inside inside our functions in our business layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have to keep up with all of them per all of our functions that call them.

Thanks,
Ron

That is sometimes a 'heated' discussion. How it goes usually depends upon whether the speakers background comes from the development world or the database world.

The Developer wants full and unfettered access to the data in order to easily create an application to solve a problem. The DBA wants to protect the data at all costs.|||

Your tests may indicate a wash but SQL Server cannot cache a query plan for embedded SQL.

It isn't scalable.

Dynamic SQL has god-awful performance issues.

I cannot believe there is even a debate in this forum surrounding embedded SQL. i've read some of the other posts - and i can tell you that the reason DBAs do not like and frequently don't even allow dynamic/embedded SQL is because THEY KNOW MORE ABOUT SQL SERVER than developers. I couldn't write a connection string in C## to save my behind, but I can tell you that you should not under any circumstances use embedded SQL, and I know this from 10+ years of SQL Server experience. I've seen it used to hack. I've seen it bring a server to it's knees.

perhaps the developers who favor embedded or dynamic SQL have never monitored the SQL server during the use of such code. (?)

once you get actual data and multiple users hitting your database, in a real world environment, you WILL NOT see a 'wash' on performance.

The security issues are even worse. No organization that goes through even minimal security audits will allow embedded SQL in an application.

SQL Server may appear to be a fairly user friendly database, and it has a lot of stuff built in that makes it seem as if almost anyone can be a DBA. This is misleading. It is not just another MS Access "database".

If I were you, I would listen to actual DBAs on this issue.

Wednesday, March 7, 2012

PIVOT with dynamic columns names created

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900

What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @.period but it doesn't work.

Thanks in advance.

Kenny

This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.

Itzik has written a good article on this.

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

Saturday, February 25, 2012

PIVOT TABLE query !! @SNMSDN

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

Pivot table error

I'm calling an Stored in the office picit tabe control from an ASP page & get this error.
Note - I'm using Dynamic SQL withing the SP
Has anybody seen this
Error
==== Data provider or other service returned an E_FAIL status
Pivot table control
=========== <object classid="clsid:0002E55A-0000-0000-C000-000000000046" id="PivotReport" codebase="http://download.microsoft.com/download/officexpstandard/owc10/2/w98nt42kme/en-us/owc10.exe" VIEWASTEXT><param name="XMLData" value="<xml xmlns:x="urn:schemas-microsoft-com:office:excel"><x:PivotTable>Hi Folk
i found the reason why it was failin
1. In my SP there was Order by clause was missin
2. Select * from <TempTable> was causing this issu

Monday, February 20, 2012

pivot report

Howdy,

don't know where to start; it sounds trivial but i need some tip/hint on this one. it might need some dynamic sql with ref cursors but I'm not sure about the simpliest way...
this is the case:

i got this
+++++++++++
|Parent| Child|
+++++++++++
| 0600 |0700|
| 0600 |0701|
| 0600 |0702|
+++++++++++

and i like to transpose this to a table/query, looking like this.
+++++++++++++++++++++++++
|Parent | Child1 | Child2 | Child3|
+++++++++++++++++++++++++
| 0600 | 0700 | 0701 | 0702|
+++++++++++++++++++++++++

knowing the fact that I don't know how many children each parent has.
any idea's? it will be highly appriciated...

thanks a lot.

Hi Jetana,

There is an excellent article about PIVOT by Peter Larsson.

He takes care of every thing and even performance too.

Follow the link.

Pivot table for Microsoft SQL Server

Have a look and use it.

Thanks

Naras.

|||

Jetana:

Here might be one way of doing what you want:

declare @.hierarchy table
( parent integer,
child integer
)

insert into @.hierarchy
select 600, 700 union all
select 600, 701 union all
select 600, 702 union all
select 601, 800 union all
select 601, 801

select distinct
parent,
replace (
( select convert(varchar(11), child) as [data()]
from @.hierarchy b
where a.parent = b.parent
for xml path('')
), ' ', ', ') as children
from @.hierarchy a

/*
parent children
-- -
600 700, 701, 702
601 800, 801
*/

|||

Alright,

I'll try to be more specific.

Kent, your solution is cool, but it's not quite what I need, because I don't know the number of childs of each parent, so I can't hardcode them.

Naras, thanks but I already found that article while googling and it's way too complicated for my case.

Here's a solution I found, but I still have a problem, let me explain:

I got my 2 tables CHILD and PARENT, where CHILD is related to PARENT with an external key. here's a view I made that contains the parent with his children (a simple LEFT JOIN):

+++++++++++++++++++++++++++++
|Parent_ID | Child_Name |
+++++++++++++++++++++++++++++
| 1 | Child1_1
| 1 | Child1_2
| 1 | Child1_3
| 2 | Child2_1
| 2 | Child2_2
| 3 | Child3_1
| 4 | Child4_1
| 4 | Child4_2
| 4 | Child4_3
| 4 | Child4_4
| 4 | Child4_5
+++++++++++++++++++++++++++++

That's the code I made:

Code Snippet

declare @.RowNum int

declare @.asdf varchar(max)

declare @.MaxRow int

-- here I get the maximum number of children

--(it will be the number of columns of the result)

set @.MaxRow = (SELECT MAX(P.noChilds) FROM ( SELECT COUNT(Parent_ID) as noChilds

FROM view_ParentChild GROUP BY Parent_ID) P)

-- here I start to build the query string

set @.asdf = 'SELECT

Parent_ID,'

set @.RowNum = 0

-- for the number of children, and for each parent, I select

-- the children's name, or I put NULL if there's no more children

WHILE @.RowNum < @.MaxRow

BEGIN

set @.RowNum = @.RowNum + 1

set @.asdf = @.asdf + 'MAX(CASE ChildNo WHEN '+CAST(@.RowNum AS varchar(10))+' THEN Child_Name ELSE NULL END) AS [Child'+CAST(@.RowNum AS varchar(10))+'],'

END

set @.asdf = LEFT(@.asdf, LEN(@.asdf)-1) --I remove the last comma

-- here I build a query that contains the parent with all their

-- children, and I assign a number to each childre (1, 2, 3...)

-- for each parent.

set @.asdf = @.asdf +

' FROM (

SELECT

Parent_ID,

Child_Name,

ROW_NUMBER() OVER (PARTITION BY Parent_ID ORDER BY Child_Name) ChildNo

FROM

view_ParentChild

GROUP BY

Parent_ID,

Child_Name) C1

GROUP BY Parent_ID;'

EXEC(@.asdf)

And here's the result:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

So now I got what I wanted, but the problem is: I cannot put this code in a view! it says "The Declare cursor SQL construct or statement is not supported."

Any Idea how to solve that?

Thanks!

|||"... Kent, your solution is cool, but it's not quite what I need, because I don't know the number of childs of each parent, so I can't hardcode them. ..." What do you mean hardcoded? This is not hardcoded; it should be able to handle an indefinite number of children; moreover, it doesn't use cursors and it doesn't use dynamic SQL.|||

You're right Kent, I made a mistake.

Sorry about that...

I still need the children's value to be in different columns though.

|||

jetana wrote:

I cannot put this code in a view!

Absolutly you can't put this logic in your view. Since the number of columns are dynamic you can't achive this directly.

Recommanded solution :

Have a Staging Table populate the data (recreate the data & structre) & reuse it on rest of the palces.

Disadvantage:

There might be some latency occur.

|||

Alright then,

I think this answers my question.

Thanks to everybody for your help!

pivot on dynamic columns

I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.

I can get the rows needed:

guid1 guid2 guid3 guid4 guid5
================================
0 1 1 0 0
0 1 1 0 1

I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:

NAME Count
===========
guid1 0
guid2 2
guid3 2
guid4 0
guid5 1

I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:

SELECT NAME
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(#Temp)
ORDER BY COLID

NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.

I also don't want to use a cursor to construct a table.

Thanks for any help,
Jameshttp://www.sqlteam.com/item.asp?ItemID=2955

HTH

Monday, March 26, 2012

Please Help

Friday, March 23, 2012

Please Help

Please Help

Tuesday, March 20, 2012

Planning on going with dynamic SQL, but...

Wednesday, March 7, 2012

PIVOT with dynamic columns names created

Saturday, February 25, 2012

PIVOT TABLE query !! @SNMSDN

PIVOT TABLE query !!

PIVOT TABLE query !!

Pivot table error

Monday, February 20, 2012

pivot report

pivot on dynamic columns

pivot information

Blog Archive

About Me