How Can I Write A Sproc For Dynamic Columns

Nov 27, 2007

Hi...

Is it possible to eliminate values from a select statements if they are NULL or Blank..

the reason i have use cast as decimals is because that value is a varchar in my database... and i want to eliminate those values from my Final select statement that dont is blank so that i will have a data set which may be only 1 - 10 long...

 ALTER PROCEDURE [dbo].[rpt_ParticipantPlanPeriodInvActivity]
@PlanId int,
@ParticipantId int,
@PeriodId int

AS
DECLARE @tbl table
(
tblId smallint IDENTITY(1,1),
ParticipantId int,
LoanId int,
Name1 char(2),
NDesc1 char(30),
TotAct1 decimal(19,4),
Name2 char(2),
NDesc2 char(30),
TotAct2 decimal(19,4),
Name3 char(2),
NDesc3 char(30),
TotAct3 decimal(19,4),
Name4 Char(2),
NDesc4 char(30),
TotAct4 decimal(19,4),
Name5 char(2),
NDesc5 char(30),
TotAct5 decimal(19,4),
Name6 char(2),
NDesc6 char(30),
TotAct6 decimal(19,4),
Name7 char(2),
NDesc7 char(30),
TotAct7 decimal(19,4),
Name8 char(2),
NDesc8 char(30),
TotAct8 decimal(19,4),
Name9 char(2),
NDesc9 char(30),
TotAct9 decimal(19,4),
Name10 char(2),
NDesc10 char(30),
TotAct10 decimal(19,4),
Name11 char(2),
NDesc11 char(30),
TotAct11 decimal(19,4),
Name12 char(2),
NDesc12 char(30),
TotAct12 decimal(19,4),
Name13 char(2),
NDesc13 char(30),
TotAct13 decimal(19,4),
Name14 char(2),
NDesc14 char(30),
TotAct14 decimal(19,4),
Name15 char(2),
NDesc15 char(30),
TotAct15 decimal(19,4),
Name16 char(2),
NDesc16 char(30),
TotAct16 decimal(19,4),
Name17 char(2),
NDesc17 char(30),
TotAct17 decimal(19,4),
Name18 char(2),
NDesc18 char(30),
TotAct18 decimal(19,4),
Name19 char(2),
NDesc19 char(30),
TotAct19 decimal(19,4),
Name20 char(2),
NDesc20 char(30),
TotAct20 decimal(19,4)
)

Insert Into @tbl
SELECT
pf.ParticipantId,
pf.FundId as LoanId,
--CASE When FundName Is Null Then ShortName ELSE FundName END as FundNames,
--pf.PortfolioId,
--PortfolioName,
Act1 as Name1,
a.Description as NDesc1,
cast(TotAct1 as decimal(19,4)) ,
Act2 as Name2,
b.Description as NDesc2,
Cast(TotAct2 as decimal(19,4)),
Act3 as Name3,
c.Description as NDesc3,
Cast(TotAct3 as decimal(19,4)),
Act4 as Name4,
d.Description as NDesc4,
Cast(TotAct4 as decimal(19,4)),
Act5 as Name5,
e.Description as NDesc5,
Cast(TotAct5 as decimal(19,4)),
Act6 as Name6,
fi.Description as NDesc6,
Cast(TotAct6 as decimal(19,4)),
Act7 as Name7,
g.Description as NDesc7,
Cast(TotAct7 as decimal(19,4)),
Act8 as Name8,
h.Description as NDesc8,
Cast(TotAct8 as decimal(19,4)),
Act9 as Name9,
i.Description as NDesc9,
Cast(TotAct9 as decimal(19,4)),
Act10 as Name10,
j.Description as NDesc10,
Cast(TotAct10 as decimal(19,4)),
Act11 as Name11,
k.Description as NDesc11,
Cast(TotAct11 as decimal(19,4)),
Act12 as Name12,
l.Description as NDesc12,
Cast(TotAct12 as decimal(19,4)),
Act13 as Name13,
m.Description as NDesc13,
Cast(TotAct13 as decimal(19,4)),
Act14 as Name14,
n.Description as NDesc14,
Cast(TotAct14 as decimal(19,4)),
Act15 as Name15,
o.Description as NDesc15,
Cast(TotAct15 as decimal(19,4)),
Act16 as Name16,
p1.Description as NDesc16,
Cast(TotAct16 as decimal(19,4)),
Act17 as Name17,
q.Description as NDesc17,
Cast(TotAct17 as decimal(19,4)),
Act18 as Name18,
r.Description as NDesc18,
Cast(TotAct18 as decimal(19,4)),
Act19 as Name19,
s.Description as NDesc19,
Cast(TotAct19 as decimal(19,4)),
Act20 as Name20,
t.Description as NDesc20,
Cast(TotAct20 as decimal(19,4))
FROM

ParticipantPlanFundBalances1 pf
Left Outer JOIN Fund f
On f.FundId = pf.FundId
LEFT Join PlanPortfolio p
On pf.PortfolioId = p.PortfolioId
Left outer Join AscActCodes a
on pf.Act1 = a.Name
left outer Join AscActCodes b
on pf.Act2 = b.Name
left outer Join AscActCodes c
on pf.Act3 = c.Name
left outer Join AscActCodes d
on pf.Act4 = d.Name
left outer Join AscActCodes e
on pf.Act5 = e.Name
left outer Join AscActCodes fi
on pf.Act6 = fi.Name
left outer Join AscActCodes g
on pf.Act7 = g.Name
left outer Join AscActCodes h
on pf.Act8 = h.Name
left Outer Join AscActCodes i
on pf.Act9 = i.Name
left Outer Join AscActCodes j
on pf.Act10 = j.Name
left outer Join AscActCodes k
on pf.Act11 = k.Name
left outer Join AscActCodes l
on pf.Act12 = l.Name
left outer Join AscActCodes m
on pf.Act13 = m.Name
left outer Join AscActCodes n
on pf.Act14 = n.Name
left outer Join AscActCodes o
on pf.Act15 = o.Name
left outer Join AscActCodes p1
on pf.Act16 = p1.Name
left outer Join AscActCodes q
on pf.Act17 = q.Name
left outer Join AscActCodes r
on pf.Act18 = r.Name
left outer Join AscActCodes s
on pf.Act19 = s.Name
left outer Join AscActCodes t
on pf.Act20 = t.Name
WHERE
pf.FundId = 0
AND
PeriodId = @PeriodId
AND
pf.PlanId = @PlanId
AND
pf.ParticipantId = @ParticipantId


--Get the Fund information for the report and combine it with the Loan information
-- in the table variable...

SELECT
pf.ParticipantId,
pf.PortfolioId,
PortfolioName,
pf.FundId LoanFundId,
CASE When FundName Is Null Then ShortName ELSE FundName END as FundNames,
Act1 as Name1,
a.Description as NDesc1,
Cast(TotAct1 as decimal(19,4)),
Act2 as Name2,
b.Description as NDesc2,
Cast(TotAct2 as decimal(19,4)),
Act3 as Name3,
c.Description as NDesc3,
Cast(TotAct3 as decimal(19,4)),
Act4 as Name4,
d.Description as NDesc4,
Cast(TotAct4 as decimal(19,4)),
Act5 as Name5,
e.Description as NDesc5,
Cast(TotAct5 as decimal(19,4)),
Act6 as Name6,
fi.Description as NDesc6,
Cast(TotAct6 as decimal(19,4)),
Act7 as Name7,
g.Description as NDesc7,
Cast(TotAct7 as decimal(19,4)),
Act8 as Name8,
h.Description as NDesc8,
Cast(TotAct8 as decimal(19,4)),
Act9 as Name9,
i.Description as NDesc9,
Cast(TotAct9 as decimal(19,4)),
Act10 as Name10,
j.Description as NDesc10,
Cast(TotAct10 as decimal(19,4)),
Act11 as Name11,
k.Description as NDesc11,
Cast(TotAct11 as decimal(19,4)),
Act12 as Name12,
l.Description as NDesc12,
Cast(TotAct12 as decimal(19,4)),
Act13 as Name13,
m.Description as NDesc13,
Cast(TotAct13 as decimal(19,4)),
Act14 as Name14,
n.Description as NDesc14,
Cast(TotAct14 as decimal(19,4)),
Act15 as Name15,
o.Description as NDesc15,
Cast(TotAct15 as decimal(19,4)),
Act16 as Name16,
p1.Description as NDesc16,
Cast(TotAct16 as decimal(19,4)),
Act17 as Name17,
q.Description as NDesc17,
Cast(TotAct17 as decimal(19,4)),
Act18 as Name18,
r.Description as NDesc18,
Cast(TotAct18 as decimal(19,4)),
Act19 as Name19,
s.Description as NDesc19,
Cast(TotAct19 as decimal(19,4)),
Act20 as Name20,
t.Description as NDesc20,
Cast(TotAct20 as decimal(19,4))

FROM

ParticipantPlanFundBalances1 pf
Left Outer JOIN Fund f
On f.FundId = pf.FundId
LEFT Join PlanPortfolio p
On pf.PortfolioId = p.PortfolioId
Left outer Join AscActCodes a
on pf.Act1 = a.Name
left outer Join AscActCodes b
on pf.Act2 = b.Name
left outer Join AscActCodes c
on pf.Act3 = c.Name
left outer Join AscActCodes d
on pf.Act4 = d.Name
left outer Join AscActCodes e
on pf.Act5 = e.Name
left outer Join AscActCodes fi
on pf.Act6 = fi.Name
left outer Join AscActCodes g
on pf.Act7 = g.Name
left outer Join AscActCodes h
on pf.Act8 = h.Name
left Outer Join AscActCodes i
on pf.Act9 = i.Name
left Outer Join AscActCodes j
on pf.Act10 = j.Name
left outer Join AscActCodes k
on pf.Act11 = k.Name
left outer Join AscActCodes l
on pf.Act12 = l.Name
left outer Join AscActCodes m
on pf.Act13 = m.Name
left outer Join AscActCodes n
on pf.Act14 = n.Name
left outer Join AscActCodes o
on pf.Act15 = o.Name
left outer Join AscActCodes p1
on pf.Act16 = p1.Name
left outer Join AscActCodes q
on pf.Act17 = q.Name
left outer Join AscActCodes r
on pf.Act18 = r.Name
left outer Join AscActCodes s
on pf.Act19 = s.Name
left outer Join AscActCodes t
on pf.Act20 = t.Name
WHERE
pf.FundId <> 0
AND
PeriodId = @PeriodId
AND
pf.PlanId = @PlanId
AND
ParticipantId = @ParticipantId




Union

SELECT
ParticipantId,
0,
'NA',
LoanId,
'Loan ' + cast(tblId as char(1)),
Name1,
NDesc1,
Cast(TotAct1 as decimal(19,4)),
Name2,
NDesc2,
Cast(TotAct2 as decimal(19,4)),
Name3,
NDesc3,
Cast(TotAct3 as decimal(19,4)),
Name4,
NDesc4,
Cast(TotAct4 as decimal(19,4)),
Name5,
NDesc5,
Cast(TotAct5 as decimal(19,4)),
Name6,
NDesc6,
Cast(TotAct6 as decimal(19,4)),
Name7,
NDesc7,
Cast(TotAct7 as decimal(19,4)),
Name8,
NDesc8,
Cast(TotAct8 as decimal(19,4)),
Name9,
NDesc9,
Cast(TotAct9 as decimal(19,4)),
Name10,
NDesc10,
Cast(TotAct10 as decimal(19,4)),
Name11,
NDesc11,
Cast(TotAct11 as decimal(19,4)),
Name12,
NDesc12,
Cast(TotAct12 as decimal(19,4)),
Name13,
NDesc13,
Cast(TotAct13 as decimal(19,4)),
Name14,
NDesc14,
Cast(TotAct14 as decimal(19,4)),
Name15,
NDesc15,
Cast(TotAct15 as decimal(19,4)),
Name16,
NDesc16,
Cast(TotAct16 as decimal(19,4)),
Name17,
NDesc17,
Cast(TotAct17 as decimal(19,4)),
Name18,
NDesc18,
Cast(TotAct18 as decimal(19,4)),
Name19,
NDesc19,
Cast(TotAct19 as decimal(19,4)),
Name20,
NDesc20,
Cast(TotAct20 as decimal(19,4))
FROM @tbl


Any help will be appreciated. 

Regards

Karen

 

View 10 Replies


ADVERTISEMENT

Write Sproc Recordset Into A Temp Table

Jul 12, 2006

I need to call a sproc about 1000 times and build a table from all the results.

How do I write an insert statement that will take the recordsets from the sproc and put it into a temp table?

View 1 Replies View Related

Using Text Data Type In Dynamic Sql Sproc

Jul 20, 2005

hi allthis is my first post to this group, so pls bear with me while i tryto make some sense.i am trying to create a sproc which uses dynamic sql to target aparticuar table eg. '[dbo].[' + @tableID + '_articles']' and performsome actions.i am using @tableID [int] as a passes parameter for the sproc.everything seems to work fine until i try and manipulate a parameterwhich is of text data type.the error occurs when i try to build the dynamic sql string and appendthe text type variable.eg.CREATE PROCEDURE [procArticlesInsert](@siteID [int],@strShortTitle [varchar](40),@strLongTitle [varchar](60),@strShortContent [text],@strLongContent [text],@intSectionID [int],@intTemplateID [int],@intStatusID [int])ASDECLARE @strSQL varchar (1000)DECLARE @strSiteID varchar (10)SET @strSiteID = CAST(@siteID AS varchar)SET @strSQL = ('INSERT INTO [' + @strSiteID + '_articles] ' +'( [dateEntered], ' +'[shortTitle], ' +'[longTitle], ' +'[shortContent], ' +'[longContent], ' +'[sectionID], ' +'[templateID], ' +'[statusID]) ' +'VALUES ' +'(' + CAST(GETDATE() AS VARCHAR) + ', ' +'''' + @strShortTitle + ''', ' +'''' + @strLongTitle + ''', ' +''''@strShortContent , ' +'@strLongContent , ' +CAST(@intSectionID AS VARCHAR) + ', ' +CAST(@intTemplateID AS VARCHAR) + ', ' +CAST(@intStatusID AS VARCHAR) + ')')GOi could cast the text fields (@strShortContent , @strLongContent) tovarchar, but the restriction of 8000 characters will not go down sonicely.if anyone has any ideas or alternatives to what i am trying toachieve, i would love to hear from you.thanksadrian.

View 5 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

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

View 1 Replies View Related

How To Write This In Dynamic Sql

Feb 7, 2007

CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As

declare @sql varchar(8000)

select u.userid,u.user_name,u.password,c.code_description as role_code,u.expiry_date,u.created_date,u.active from [usermaster] u inner join [codeMaster] c
on u.role_code=c.code where u.userid like'1%'
order by u.userid

GO

in this stored procedure after where condition i need to use the u.@columnname like '@recordname' order by u.@columnname

how to use this lines in dynamic sql pease help me to do

View 8 Replies View Related

How To Write This In Dynamic Sql

Feb 10, 2007

CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
declare @count bigint
declare @sql varchar(8000)

begin
select @sql='select ' + @count + '=count(*) from usermaster where ' + @columnname + ' like ''' + @recordname + '%'' '
exec(@sql)
end
GO

if i use like this it gives error,please help me to rectify this error

View 9 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

How To Write Query From Dynamic Table Name

Jun 19, 2002

Hi,

Please advice me how to write query from table which is given as input(It is not hard coded)


Declare @varTableName varchar(30)

Select @varTableName = 'table_employee' -- table name

Select * from @varTableName

This gives me an error : Must declare the variable '@varTableName'.

View 2 Replies View Related

Want To Write A Query Which Select The Columns Passed As Variable In Sql Sp

May 2, 2008

i want to select the values of column passed from the user like


connectionsizein or connectionsizemm

how can i do

i am using sql server 2005

View 2 Replies View Related

Dynamic Columns For Dynamic SQL

Mar 9, 2007

I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.

Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.

I have come to understand that a dynamic anything is a moving target!



Please advise.

View 3 Replies View Related

Dynamic Columns

Mar 18, 2004

Hi all, a quicky.

What is wrong with:

CREATE PROCEDURE dbo.QuotePrice
@DateFrom datetime,
@DateTo datetime
AS
declare @days as int
declare @price as smallint
declare @daycolumn as nvarchar(6)

set @price = 0

set @days = DATEDIFF(day, @DateFrom, @DateTo)

set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

set @price = (SELECT @daycolumn FROM pasPriceTable)

return @price
GO


Problem is:
Syntax error converting the nvarchar value 'Day_10' to a column of data type smallint

How do I set a variable to a result from a query?
Thanks,
/ j0rge

View 9 Replies View Related

Dynamic Columns

Sep 6, 2007

Hi all again,I need an output like :|----|----|----|---|------||row1|row2|row3|...|row(n)||----|----|----|---|------|is possible to create output like that, without querying manuallycause too many dynamic columns in my application.sorry if my question a bit weird cause i'm a newbie in MSSQL.Cheers.

View 1 Replies View Related

Dynamic Columns

Sep 20, 2007

My report has a large number of rows in the details section, This wastes a lot of space. I'd like to spread these rows across multiple columns in the details section of my table.

What i'm currently getting...
---ROW1----------
---ROW2----------
---ROW3----------
---ROW4----------
---ROW5----------
---ROW6----------
---ROW7----------
---ROW*----------
---ROW9----------

What I'd like to see...

---ROW1---------- ---ROW2---------- ---ROW3----------
---ROW4---------- ---ROW5---------- ---ROW6----------
---ROW7---------- ---ROW8---------- ---ROW9----------

Is this possible, if so, how??

View 4 Replies View Related

Are Dynamic Columns Possible ?

Mar 19, 2007

Hi folks

I'm trying to roll out / createcolumns dynamically, based on a parameter selection, which in turn can calculate the number of columns to be inserted.

For example, if my parameter selects the first Option viz. Business Unit, I need to find out how many standard business units exist (un-specified and depending on the client) and have that many standard width columns generated and have each populated with a column header label + appropriate value.

the columns have to be dynamic and the same information is preferred if it is NOT presented in a drill down format (row-wise).



Thanks for any inputs coming in :)

Prashanth

View 1 Replies View Related

Dynamic Number Of Columns

Jun 2, 2005

when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

View 5 Replies View Related

Dynamic Columns Returned

Sep 6, 2006

I need to write a query that will return an unknown number of columns. Here's the problem:

Let's say you've got a database tracking applications to different university study abroad programs. There may be a dozen or more programs, and the application could be used at any number of different schools, each with different programs. So you have a programs table with ProgramName and ProgramID.

Each program could require a different set things submitted during the application process. So you have a Submissions table, with SubmissionItem and SubmissionID to list the names of each required submission, a Program_Submissions table, with ProgramID and SubmissionID to link Programs to required application items, a SubmittedItems table, that accepts documents uploaded by a user to fullfill the submission requirements, and of course a users table.

I'm not very concerned at the moment about the last two tables. What I need to do is create a report (query) where the first column is the program name, and the other columns are determined by the contents of the Submissions table- if Program_Submissions record exists linking a program to a submission, place a value of 'Y' in that cell, otherwise leave it blank. Any thoughts on how to accomplish this?

I need to do this in a single query that a I can dump into a report builder application to save as a template to give to several people that have been asking for it, and to complicate matters even more, the description above is a little simple, as there are couple different kinds of submissions, with a different table for each.

Any help appreciated. I give rep for good answers!

View 3 Replies View Related

Selecting Dynamic Columns

Jul 12, 2004

I have the following Tables:

Table1: Row1
----------------------------------
Name Gary
Garbage1 A
Garbage2 B

Table2: Row1 Row2 Row3
------------------------------------------------------------------
Name Gary Gary Gary
Value 1 2 3
Desc Day Afternoon Night

Table1 has a 1 to Many relationship with Table2

Id Like to have the select statement Return the data as follows:

Gary,A,B,Day,1,Afternoon,2,Night,3

not like this:
Gary A,B,Day,1
Gary A,B,Afternoon,2
Gary A,B,Night,3

Any Ideas?

View 3 Replies View Related

Pivot On Dynamic Columns

May 11, 2006

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:

guid1guid2guid3guid4guid5
================================
01100
01101

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
===========
guid10
guid22
guid32
guid40
guid51

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,
James

View 1 Replies View Related

Report With Dynamic Columns

Jun 10, 2008

Hi,
I'm trying to create a report with dynamic columns. I mean that the user is able to select if he wants to see a double drill report with client group and product group, or agents with clients, or everything else he needs. I tried to insert some variables in the query that extracts data from report, and using that variables like parameters, but Reporting Services always get the parameter default value and not the correct value i give him.
How can I solve my problem? There is another way to obtain dynamic reports?

View 13 Replies View Related

Dynamic Columns In .net Reporting.

Oct 18, 2007

Hi all,
I have a problem making columns dynamic in a report. I' m using Microsoft.net reporting. I was using a flag to determine wheather a column should be made visible or hidden using
IIF(instr(Fields!numrows.value="a")>0,True,false) in the column I want to make dynamic. I do this by setting this expression in the visibility property of the column.
I have 7 columns which are to be made dynamic so I used the same condition in all the columns with a different string to compare ("a","b","c" etc) . I got the columns to be visible dynamically but the condition was not working properly so, I shifted from fields to report parameters and used IIF(parameters!fieldsDisplay1.value=true,true,false). I can get this to work. The problem I have now is if user selects col1 ,col3,col 5 among(col1,col2,col3,col4,col5,col6,col7) then the report shows up like this.... col1, ,col3, ,col5. Meaning the alternate columns are empty because I' m hiding the visibility when I use the above expressions. I want the report to show all columns one after the other example col1,col3,col5 so that the report doesn't have any empty columns.
I found a link online http://www.c-sharpcorner.com/UploadFile/uditsingh/CR1111022006055359AM/CR11.aspx which explains the same in crystal reports, Can anyone tell me if this is possible using Microsoft reporting? If yes, how do I accomplish this?
Any help would be appreciated.
Thanks
Mythili




View 6 Replies View Related

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.

Feb 13, 2007

I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View 3 Replies View Related

Dynamic Columns In SSRS Reports

May 29, 2012

I have 100+ columns and I want to create a SSRS report which has to have dynamic columns and data. The source tables looks as below:

(First row has column names)

Formula1 Formula1_Amount Formula2 Formula2_Amount .........
Pen 1001 Pencil 100

View 1 Replies View Related

How To Create Dynamic Columns In Sql Database

May 3, 2008

hi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.

Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3


help is appreciated

View 1 Replies View Related

Report Whit Dynamic Columns

Jun 11, 2008

But If I change default parameter with everyone I need it always run correctly! The real problem is in execution mode, it not takes the parameter i give it danamically. I can see only the default columns I set by default. The thing I need to kwnow is: is this method correct or this problem can be solved in any other manner?

View 2 Replies View Related

Dynamic Union All - Output More Columns

Apr 29, 2014

I am having a bit of trouble writing a query that dynamically outputs what I need.

I have a table that looks like this:

COL1 AAA BBB CCC
CAT 1 3 3
CAT 1 2 2
CAT 1 4 4
DOG 2 3 3
DOG 5 5 5
MICE 1 1 1

I need it to dynamically output based off COL1, the output should look like this. When there are more rows for CAT, it should output more columns. Kind of like merging the columns

COL1 AAA BBB CCC AAA BBB CCC AAA BBB CCC
CAT 1 3 3 1 2 2 1 4 4
DOG 2 3 3 5 5 5 NULL NULL NULL
MICE 1 1 1 NULL NULL NULL NULL NULL NULL

View 3 Replies View Related

Transpose Dynamic Rows To Columns

Mar 23, 2015

how to transpose rows to columns . there are 800 dynamic rows

device_idUserNameipaddrmacaddrLastConnecttimeOperatingSystemRAMCPUTOTALSIZEFREESPACEpercentageDriveNameName
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 Plugin [15.0.0.239]

[code]...

View 9 Replies View Related

Help With A Pivot Table (dynamic Columns)

Feb 15, 2008

I have the following Pivot Table:



Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable






What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID ProcessorType
23 P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View 4 Replies View Related

Stored Procedures / Dynamic Columns

Mar 23, 2006

I want to use parameters within a stored procedure to generate dynamic columns using SUSER_SNAME as the name for the column that I want to dynaically select (e.g. Select @SUSER_SNAME, First, Last, City FROM MyTable). I have been able to successfully use parameters in the WHERE clause within a stored procedure but haven't been able to find a way to use parameters for column names let alone to tie the parameter value back to SUSER_SNAME.

Any insight would be greatly appreciated!

View 4 Replies View Related

Searching In Columns Dynamically Without Dynamic SQL

May 8, 2007

Hi everyone



Is it possible to search dynamically in table columns without using dynamic SQL (i.e. EXEC or sp_executesql)?

I wanna exec FREETEXT(<column_name>, @searchPhrase) by giving columns' names as paramater to stored proc. Is it possible to avoid using dynamic SQL?



Thanks in advance

View 8 Replies View Related

Dynamic And Changing Columns Problem

Dec 1, 2007

Hi all,
I am dealing with a quite complicated problem - I have to build a report with dynamic columns based on table rows. Each row in the table representes a column. My real problem is that the columns are changing all the time, so even if I am using C# to build the report dynamically I cannot use SSRS subscription (because the rdl file need to be on reports server and it is not updated). A matrix is not suitible becase some of the columns are static (mayebe in SQL 2008 there will be a solution with the new matrix - table control).
I thought of two solutions to the problem:
1. Build the report dynamically with c# and update it on the server each time a row in the database is changed. Very complicated to manage and can have a lot of problems.
2. Use some kind of formula (mayebe with VB custom function code) to determine the rows dynamically). I am not sure this is even posible. I have to use a table with grouping when each group with a diferrent unknown size columns.

Did someone have a similar propblem? I will love to hear an idias / solusions.

Thanks

Yaniv

View 1 Replies View Related

Dynamic Time On Columns With Reporting Services

May 17, 2005

Hi all!

If I have some mdx I'm using in reporting services like this:

select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls

The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.

I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.

What do you think? Would a matrix be more flexible in this case?

Richard

View 7 Replies View Related

Dts Dynamic Query && Automap Columns Question

Jul 28, 2006

Hi There,

I have designed a DTS package which extracts a query into an excel file.

It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

Currently, the DTS package errors on each attempt because it expects a certain column set.

Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

I would hate to have to set up a different DTS package for each possible column set.

I am sure I am missing something.

Thanks in advance.

- Charles

View 2 Replies View Related







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