Dynamic Column In The Query Using SQL 2005

Aug 11, 2006

Hi All,

 I am using Micosoft Visual Studio Report Desinger. with MS SQL 2005.

I have a table transac table fields are likely,

location,date,amount values,

USA,01/07/2006,3000

SG,01/07/2006,2500

USA,02/07/2006,6000

SG,02/07/2006,3500

USA,03/07/2006,1000

SG,03/07/2006,6700

USA,04/07/2006,500

SG,04/07/2006,200

Am writing query for date = 04/07/2006

select location,date,amount from transac where date = 04/07/2006

I wanted to add two more column in the query which is

a.two days before what is the amount

b. From 01/07/2006 to 04/07/2006 what is the amount

 

The result I want to be

Location,date,amount,2daysbefore,uptodate

USA,04/07/2006,500,6000,10500

SG,04/07/2006,200,3500,12900

How to write a query ?.

I am writing this query from DataSet for Report Desinger.

Is there any way to include this two column.

Please Advise,

Regrads Saleem

View 1 Replies


ADVERTISEMENT

Dynamic Column Amount Query

Jul 23, 2005

I want to write a query that will give me a dynamic amount of columns back.What I want to do, I want to create a calendar application, in which foreach employee, I want to show if he is in the office or not.this should look like:ID, Name, 1,2,3,4,5,6,7,888,Leo,0,0,1,1,1,0,0,1The amount of columns is dynamic, and is a period of time, with a column foreach day.Any suggestions what the best approach to this could be?ThanksLeo

View 3 Replies View Related

Dynamic Query Help SQL 2005

Feb 13, 2006

I'm having a problem in getting a dynamic query in a sp working and thecode is below so can someone please help me? It would be very muchappreciated.ThanksGirogio--------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[Search_Profile]@Country NVARCHAR(100) = null,@County NVARCHAR(100) = null,@Town NVARCHAR(100) = null,@AType bit,@PageIndex int,@NumRows int,@UsersCount int OutputASBEGINDECLARE @where_clause NVARCHAR(500);IF @Country IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCountry = "' + @Country +'"'ENDIF @County IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCounty = "' + @County + '"'ENDIF @Town IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aTown = "' + @Town + '"'ENDIF @AType IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND Independent = "' +Convert(NVARCHAR, @AType) + '"'ENDDECLARE @Query1 NVARCHAR(1000);SET @Query1 = 'SELECT @UsersCount=(SELECT COUNT(*) FROM CustomProfileWHERE aActive = 1 ' + @where_clauseexec(@Query1)DECLARE @startRowIndex int;SET @startRowIndex = (@PageIndex * @NumRows) +1;DECLARE @Query2 NVARCHAR(1000);SET @Query2 = 'WITH UsersProfiles as (SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,t.apubName, t.aCounty, t.aTown, u.UserNameFROM CustomProfile t, vw_aspnet_Users uWHERE t.UserID = u.UserID AND aActive = 1 ' + @where_clauseDeclare @Query3 NVARCHAR(1000)SET @Query3 = 'SELECT UserId, apubName, aCounty, aTown, UserNameFROM ' + @Query2 +' WHERE Row BETWEEN ' + Convert(NVARCHAR, @startRowIndex) + ' AND ' +Convert(NVARCHAR, @startRowIndex+@NumRows-1)exec(@Query3)END

View 2 Replies View Related

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,


Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Column Data To Column Heading By Dynamic Pivot Maybe

Feb 27, 2008



Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41

StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41

The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.

View 9 Replies View Related

Am Using Sql Server 2005, Column Data Type Is Varchar, How To Write A Query To Sort

Aug 2, 2006

this data. need help
Sort following numbers by asc and desc order

Before query sort
-1.1
-8.8
-15.5
0.0
+0.5
+0.2

Sort asc
+0.5
+0.2
0.0
-1.1
-8.8

Sort Desc
-8.8
-1.1
0.0
+0.2
+0.5












View 5 Replies View Related

Dynamic Column Name (using Where)

Jun 13, 2007

Hello All,

I'm trying to us a parameter as column name like:

Select * From SM_Clients
where @Selection like @SelectionChar+'%'

This dos not work.

does someone have a solution :(

tks stroek

View 11 Replies View Related

Dynamic Column Name (using Where)

Jun 13, 2007

Hello All,

I'm trying to us a parameter as column name like:

Select * From SM_Clients
where @Selection like @SelectionChar+'%'

This dos not work.

does someone have a solution

tks stroek

View 5 Replies View Related

Dynamic Column Names In A SP

Jan 1, 2008

Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query.  Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance 

View 2 Replies View Related

Rename The Column By Using The Dynamic Value

Feb 5, 2008

Hi All
I want to rename the column name by using the dynamic value as like
declare @name as varchar(12) set @name='ss'
sp_rename 'dbo.employees.id3',@name, 'COLUMN';
go
but it shows the error
Incorrect syntax near 'sp_rename'.
any idea?

View 5 Replies View Related

Dynamic Column Names

Jun 10, 2004

Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'

select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1

View 2 Replies View Related

Dynamic Column Names

Jan 19, 2005

thanx for the help in advance,

i have a table with Monday, Tuesday, Wednesday. .... Sunday

i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday

but when i do something like

select top 2 * from myTable
where 'myTable.'+@stat = 1

which is as same as

select top 2 * from myTable
where myTable.wednesday = 1

i receive a syntax error. how can i dynamically select myTable.Wednesday ?

i also tried using
select top 2 * from Intercon
where '@stat' = '1'

but then this doesn't return anything where it should.
any help?

View 2 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Dear All,

We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 6 Replies View Related

Dynamic Column Mapping

Apr 18, 2007

Hi everyone,
So I have to export from a SQL 2005 table to a dBaseIV table using SSIS. Easy enough, however the catch is that the tables being exported will vary. I can send variables to the the package calling the sql table name and creating the DBF file with the same columns on the fly.

The problem is mapping the columns. From what I've been reading there is no way to alter the mapping in a package at runtime. I was hoping that there would be some sort of auto mapping setting that would match on the field names but I guess not. Anybody run in to this issue and have a work around? Thanks in advance....

Scott

View 1 Replies View Related

Dynamic Column Names

Aug 28, 2007

Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.

Mike
"oh, that monkey is going to pay"

View 3 Replies View Related

Dynamic Column Names

Aug 9, 2007

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:


GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:





Code Snippet

DECLARE @ColumnName varchar(128)
DECLARE @Sql varchar(255)
SELECT @ColumnName = [ColumnName] FROM tblSourceData
SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData'
EXEC (@Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

View 6 Replies View Related

Destination Column Value Dynamic

Dec 1, 2007



Hi

In SSIS i have an execute SQL task where i get the job_id as the output which i set it to a package variable.

Now what I need to do is i have to map this value to one of the columns of the OLEDB destination.

How can I do it? Please help me ASAP.

Thanks

Sai

View 3 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 4 Replies View Related

Dynamic Selection Of Column

Nov 19, 2007



Hi,
I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name.
I tried to use the following sample query.
Eg:
Create table temp
(test_id int,
test varchar(100))


Declare @var varchar(100)
Set @Var='Hi'
Select
case when @Var='HI' then "None" else Test_new End as Test
from Temp

But it throw error as "Invalid column 'Test_new' "

As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.

View 5 Replies View Related

[SQLDatasource]Dynamic Selection Of Column

Mar 29, 2006

Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?

 
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource> 

View 3 Replies View Related

Dynamic Name Of Result Column In S-proc?

Oct 12, 2004

How can I make this stored procedure concept work:

-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------

Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?

Bjorn

View 2 Replies View Related

Column Name As Parameter Dynamic Sql Alternative

Feb 11, 2008

I am unfortunately lumered with a table I cannot edit that stores a division 2 characters along with boolean fields '0506', '0607', '0708' ... (academic years) to represent if that particular division is active in the current academic year. This has made me have to resort to dynamic sql within a stored procedure to input the appropriate field names.

Is there a better way to do it, it's not mission critical to make it not use dynamic sql but I would prefer not to.

View 2 Replies View Related

Matrix Dynamic Column Problem HELP!

Jun 20, 2007

Ill start off by saying that I am an ASP.NET developer and I am self-taught in using SSRS2005. (quite poorly I might add) That being said I am having a problem with a report im building. This report has 10-12 different metrics I am reporting on and I have each of them displayed in their own matrix. The first matrix was created by the report wizard that launches when you add a new report.

To give you an overview of how I am setting up these reports I will describe the structure I am using on nearly all of the matrices. I am reporting on classes that are managed by my company and held at 3rd party locations. My matrix has the 3rd party location in the rows and the quarter the class was held is the column. Matrix setup looks like this:

________|_______Quarter____________
|MetricA|MetricB|MetricC|etc...
LocationA|
LocationB|
LocationC|

The problem Im having is this. All matrices that weren't created by the wizard don't grow dynamically. Say I have 2 quarters of data I am reporting on, Instead of having a column for quarter1 and quarter 2, the report generates separate matrices for each quarter. This is true for each matrix that I created using a matrix control from the toolbox. The matrix that was created by the wizard however, has dynamic columns that grow to the right as expected.

I am populating my datasets using Stored Procedures and the matrix that behaves normally uses a different dataset than the 4 others I have completed thus far which share a dataset.

I have looked everywhere I can think of for a checkbox or a property I need to adjust and I cant find one. Anyone have any ideas??

View 1 Replies View Related

Dynamic Reference Of Column In Input0Buffer

May 15, 2007

hello,

in the script task component - i need to refer to Row.Column9.ToString() but the Column9 is coming from a variable. meaning - at run time it can be Column9 or Column7 at other runs. how can i write Row.ColumnName.ToString() in a dynamic way? (aka that the ColumnName is dynamic and not hard coded?)

thanks!

Arik

View 1 Replies View Related

Dynamic Color For Column Chart

Oct 26, 2007



Need to be able to change the color of the columns red for negative values and Green for positive-- Im thinking that this will have to be a custom code scenario and not a expression-- any suggestions??

kam

View 1 Replies View Related

Dynamic Column Bind (Select * From..)

Jul 9, 2007

I'm looking for a way to use SSRS to display the contents of a returned dataset with 1-n columns. The number of columns is unknown at design time and the datasetview is using a stored procedure with 1 parameter to return the data. Does any one know if RDL supports wildcard(*) characters for field names or if there is another method.



I have this working via a web based custom aspx page but it would be VERY helpful to utilize all the document conversion features the SSRS reports provide via the web.



Thanks for any help.

IT Publisher

View 1 Replies View Related

Dynamic Join Based On Column Value??

May 4, 2007

Hi,

I have three tables.

The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.



I need to do a join to a second table based on the MasterID...



However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.



Make sense?



Anyone got any pointers?



Thanks in advance

James

View 8 Replies View Related

Dynamic Column Name - Hard Problem...

Aug 9, 2006

Ok, here is my problem, lets say i have the following three tables

Product_Group_Master
ProductGroupId

Size_Groups
SizeGroupCode
SizeCode1
SizeCode2
SizeCode3
SizeCode4
SizeCode5

Product_Group_Inventory
ProductGroupId
Inventory1
Inventory2
Inventory3
Inventory4
Inventory5

Individuale_Products
ProductId
ProductGroupId
SizeGroupCode
SizeCode

Ok, now that you understant the table layout... i need get a list of ProductId's with there respective inventory count

So what i need to do is select all the product id's from the "Individuale_Products" table, and the based apon there "SizeGroupCode", and "SizeCode"...
I need to look up in the "Size_Groups" table...
on the row that is equal to SizeGroupCode...
the colum name that has the value of the "SizeCode"...
So that then i can look up in the "Product_Group_Inventory" table the correct inventory column.


Please feel free to ASK any furthur Questions... or e-mail them to me at gregmoser@forwarddev.com

View 2 Replies View Related

How To Hide A Dynamic Column During Runtime?.. Help Please...

Mar 2, 2007

im working on a report which shows the data based on the users input. I have used a matrix because I have no control on how many fields the users want, so practically we have a dynamic column here. My problem is that they require me to able the user to hide the columns he/she wishes to during runtime. We have a previous report that enables the user to hide it during runtime but it has static column. Any idea on how I could do it?... Thank you so much...

View 1 Replies View Related

Dynamic Column Names In Select Statement

Oct 13, 2001

I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.

declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.

The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.

View 1 Replies View Related

Dynamic Column Selection In Stored Procedure

Feb 10, 2005

Hey Guys,

Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.

DECLARE @querycolumn varchar(20),
@queryvalue varchar(35)

SET @querycolumn = ''
SET @queryvalue = ''

IF(@sub_assembly = NULL)
BEGIN
IF(@part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @querycolumn = 'Part'
SET @queryvalue = @part
END
END
ELSE
BEGIN
SET @querycolumn = 'SubAssembly'
SET @queryvalue = @sub_assembly
END

SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @querycolumn = @queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part

The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?

View 14 Replies View Related

XML With Dynamic Attributes Based On Column Values

Apr 10, 2007

I have been banging my head against the wall for TWO days. I have
gone back and forth with a very patient guy on thescripts.com. You
can see the ridiculous thread here

http://www.thescripts.com/forum/threadnav628777-1-10.html

If you have time, at least peruse that so we don't go in circles.
Anyway, if you guys can help me solve this, I will be forever
grateful!!


Here is the "basic" problem:


Here is an example for TWO different entities in the database.


EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111


Here is how the two different results should be


where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>


where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US"
Zip="111111"/>


Notice how the attribute names (City or CityName, State or StateCode,
etc) are based off the XmlFieldName and I don't know in advance what
the possible values will be? I also don't know how many attributes
there will be, but they can be different per entity, depending on how
they have set up an address in our application.


Another thing to note, is that I kind of have this working in an sproc
using PIVOT and generating a table with the values that have the
correct dynamic column names (you can see this on my other thread I
posted above) but I REALLY need this to not use dynamic SQL (so can
use it in a function) if possible and be able to be used in a select
statement, whether it be a temp table as I would like to get a result
set back that I can do a FOR XML RAW on. If this is confusing, it is
because I am delerious. OR is there a way to return a table from an
SPROC that has dynamic columns built?


Please help!! Thanks so much!!!


Brian

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved