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
ADVERTISEMENT
May 13, 2008
Hello,
I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)
Here is the whole query in case this helps, thank you
SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and (@StartingDate is Null or annualLeave.from_date > @Startingdate)
and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave)
and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
UNION
SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and annualLeave.leave_type like 'Leave in lieu'
ORDER BY
CASE @OrderDir
WHEN 'ASC' THENannualLeave.from_date
END
ASC,
CASE @OrderDir
WHEN 'DESC' THEN annualLeave.from_date
END
DESC
View 3 Replies
View Related
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
Nov 30, 2007
Hi,
Can anyone help as to how to get this to work? dbo.parseInt is a Function which sorts Alpha Numerically. If I removed the top part of the UNION, the Procedure works fine.
ALTER PROCEDURE dbo.ByJobNoAlphaNumeric as
SELECT 0 AS JobID, '<All Jobs>' AS JobNo
UNION SELECT JobID, JobNo FROM tbl
ORDER BY dbo.parseInt(JobNo)
Regards
Andrew
View 2 Replies
View Related
Jul 25, 2007
To simulate problem do follows:
1. Add Script source component.
2. Add 2 string output collumns: F1 length 50, F2 length 110
3. Add Union All component connected to Script source component
4. Add Script transformation component connected to Union All component
5. Mark F1 and F2 as input collumns
6. In the Input and Output section of the script transformation component check that length of the fields is 50 and 110 correspondingly
7. Edit Union All component: change Union All Input 1 to <ignore> for Output collumns F1, and F2; change Union All Input 1 to F2 for F1 and F1 for F2; change back <ignore> for F1, <ignore> for F2; set back F1 for F1, F2 for F2; Click OK to save
8. In the Input and Output section of the script transformation component check that length of the both fields is 110 now.
So, be careful to select output columns values in Union All component. If you choose wrong field by accident, and replaced it with correct one straight away, the length of output column could be stored not as you expect an need.
View 5 Replies
View Related
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
Dec 19, 2007
Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.
Existing procedure:
ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)
select @Cond=''
if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)
if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow
exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int
select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl
set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;
SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')
RETURN
can anybody help me pls. ITs very urgent.
Thanks in advance.
View 1 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
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
Jul 19, 2006
I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T
UNION ALL
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T
This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.
The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.
My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?
View 2 Replies
View Related
May 10, 2007
I have two tables Encounter & Encounter_History. They have same columns. One column is of type CLOB. My requirement is to retrieve all the distinct records from both the tables with order by a date column. But problem is, UNION does not work in case of CLOB data type.
I know it will work if I use UNION ALL, but it returns duplicate records.
Please give me suggestion, how to solve this problem.
For example: The following query does not work since column1 is a CLOB data type
select column1 from table1
union
select column1 from table2
Thanks
View 1 Replies
View Related
Feb 19, 2008
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0
) AS p
GROUP BY p.id
View 9 Replies
View Related
May 23, 2006
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
TIA,
Barkingdong
View 5 Replies
View Related
Feb 7, 2005
I have a long running trigger that makes calls to several tables in order to get values for a list of parameters before doing my final INSERT statement into a different table.
One of my parameters is for a local language translation of a particular word which is stored in a table. The problem is - I do not know the name of the table until runtime when I dynamically build the name as follows:
DECLARE @SQL nVarChar(200)
SET @SQL = 'SELECT @Translation = nvcLocalEventDescription FROM Monitoring.dbo.tblSignalTemplate'
+ CAST(@MonitoringCentreID AS nVarChar) + ' WHERE nvcEventDescription = "' + @EventDescription + '"'
EXECUTE Management.dbo.usp_parmsel_LocalEventDescription @SQL, @LocalEventDescription OUTPUT
If there is a MonitoringCentreID of 1234, then there will be a table named tblSignalTemplate1234 - which will contain a nvcLocalEventDescription field containing the value that I am after. Here is the code for the stored procedure...
CREATE PROCEDURE [dbo].[usp_parmsel_LocalEventDescription]
@strSQL nVarchar(150),
@Translation nVarChar(100) OUTPUT
AS
EXECUTE sp_executesql @strSQL
GO
The error I get is "Must declare the variable '@Translation'" - which has thrown me a little as it declared on the 3rd line of the stored proc.
Anyone got any ideas where I am going wrong, or as usual, is there a simpler way ?
Steve.
View 2 Replies
View Related
Sep 21, 2004
Hi:
Can anyone tell me if it's possible in SQL Server 2000 to build dynamically a select statement and get some values to variables (simple not tables) from the select ?
Thanks,
Rui Ferreira
View 4 Replies
View Related
Jul 23, 2005
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
View 6 Replies
View Related
Jan 23, 2015
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
UNION
SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
View 2 Replies
View Related
May 5, 2008
hello,
I'd like to know how I can grab the output of the following code and use it as variable:
declare @sql nvarchar(25)
set @sql = 'SELECT CURRENT_TIMESTAMP'
EXEC(@sql)
Thank you.
View 2 Replies
View Related
Apr 2, 2015
This is my table.
CREATE TABLE tpivot
([col1] varchar(80), [col2] varchar(80), [col3] varchar(80), [col4] varchar(80), [col5] varchar(80)) ;
INSERT INTO tpivot
([col1], [col2], [col3], [col4], [col5])
VALUES
[code]...
My goal is to turn the table so that the output look like this.
col1 col2 col3 col4 col5
Datum 01.12.2014 02.12.2014 03.12.2014 04.12.2014
EC -204.9 -352.9 -105 -371
Mastercard -88 0 -182 -112.9
Postfinance 0 -79.9 0 -751
VISA -19 -20 -436 -346
I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time. I can't even get a static pivot to work.
View 1 Replies
View Related
Nov 15, 2005
The dynamic SQL statements with output parameters, unfortunately, in the documentation are not described.
À) Simple example of the dynamic SQL statement with output parameters
-- dynamic SQL statements expects parameter of type 'ntext/nchar/nvarchar'.
declare @SQLString nvarchar(4000), @ParmDefinition nvarchar(4000)
-- the third parameter passed in the dynamic statement as by output, returns the length of
-- the hypotenuses of a right triangle, two first parameters are lengths of legs of a triangle
declare @nHypotenuse float
select @SQLString = 'select @nHypotenuse = sqrt(square(@nLeg1_of_a_triangle)+square(@nLeg2_of_a_triangle))',
@ParmDefinition = '@nLeg1_of_a_triangle float, @nLeg2_of_a_triangle float, @nHypotenuse float out'
-- we call the dynamic statement in such a way
exec sp_executesql @SQLString, @ParmDefinition, @nLeg1_of_a_triangle = 3.0, @nLeg2_of_a_triangle = 4.0, @nHypotenuse = @nHypotenuse out
-- or in such a way
exec sp_executesql @SQLString, @ParmDefinition, 3.0, 4.0, @nHypotenuse out
select @nHypotenuse -- Displays 5.0
B) Example of usage of the dynamic statement with output parameter and the function GETALLWORDS.
The following stored procedure get all words from a field of the type text or ntext, the word length should not exceed 4000 characters.
CREATE PROCEDURE SP_GETALLWORDSFROMTEXT
@TableName sysname, @FieldIdName sysname, @FieldIdValue sql_variant, @FieldTextName sysname, @cDelimiters nvarchar(256) = NULL
AS
-- this Stored procedure inserts the words from a text field into the table.
-- WORDNUM int – Sequence number of a word
-- WORD nvarchar(4000) – the word
-- STARTOFWORD int – position in the text field, with which the word starts
-- LENGTHOFWORD smallint – length of the word
-- Parameters
-- @TableName name of the table with the text or ntext field
-- @FieldIdName name of Id field
-- @FieldIdValue value of Id field
-- @FieldTextName name of field text or ntext
-- @cDelimiters Specifies one or more optional characters used to separate words in the text field
begin
set nocount on
declare @k int, @wordcount int, @nBegSubString int, @nEndSubString int, @nEndString int, @divisor tinyint, @flag bit, @RetTable bit,
@cString nvarchar(4000), @TypeField varchar(13), @SQLString nvarchar(4000), @ParmDefinition nvarchar(500), @nBegSubString1 smallint, @nEndSubString1 smallint
select @TableName = object_name(object_id(lower(ltrim(rtrim(@TableName))))), @FieldIdName = lower(ltrim(rtrim(@FieldIdName))), @FieldTextName = lower(ltrim(rtrim(@FieldTextName))),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nBegSubString = 1, @nEndSubString = 4000, @flag = 0, @RetTable = 0, @wordcount = 0
-- If the temporary table is not created in the calling procedure, we create the temporary table
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is null
begin
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
select @RetTable = 1
end
-- we use the dynamic SQL statement to receive the exact name of text field
-- as we can write names of fields by a call of the given stored procedure in the arbitrary register
-- in the string of parameters definition @ParmDefinition we use a keyword output
-- and by a call of the dynamic SQL statement exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- Also we use a keyword output for definite before parameter
select @SQLString = 'select @FieldTextName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''''
select @ParmDefinition = '@FieldTextName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldTextName = @FieldTextName output
-- we use the dynamic SQL statement to receive the exact name of Id field
select @SQLString = 'select @FieldIdName = name from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldIdName+''''
select @ParmDefinition = '@FieldIdName sysname output'
exec sp_executesql @SQLString, @ParmDefinition, @FieldIdName = @FieldIdName output
-- we use the dynamic SQL statement to receive the type of field (text or ntext)
select @SQLString = 'select @TypeField = name from systypes where xtype = any ( select xtype from syscolumns where id = OBJECT_ID('''+ @TableName+''') and lower(name) = '''+@FieldTextName+''')'
select @ParmDefinition = '@TypeField varchar(13) output'
exec sp_executesql @SQLString, @ParmDefinition, @TypeField = @TypeField output
select @divisor = case @TypeField when 'ntext' then 2 else 1 end -- 2 for unicode
-- we use the dynamic SQL statement to receive a length of the text field
select @SQLString = 'select @nEndString = 1 + datalength('+ @FieldTextName+')/'+cast( @divisor as nchar(1)) +' from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@nEndString int output'
exec sp_executesql @SQLString, @ParmDefinition, @nEndString = @nEndString output
-- We cut the text field into substrings of length no more than 4000 characters and we work with substrings in cycle
while 1 > 0
begin
-- we use the dynamic SQL statement to receive a substring of a type nvarchar(4000) from text field
select @SQLString = 'select @cString = substring('+ @FieldTextName+','+cast( @nBegSubString as nvarchar(20)) +',' +
cast( @nEndSubString - @nBegSubString + 1 as nvarchar(20))+') from '+@TableName +' where '+ @FieldIdName+' = ' +cast(@FieldIdValue as nchar(50))
select @ParmDefinition = '@cString nvarchar(4000) output'
exec sp_executesql @SQLString, @ParmDefinition, @cString = @cString output
select @nBegSubString1 = 1, @nEndSubString1 = @nEndSubString - @nBegSubString +1
while charindex(substring(@cString, @nBegSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndSubString >=@nBegSubString -- skip the character not in word, if any
select @nBegSubString = @nBegSubString + 1 , @nBegSubString1 = @nBegSubString1 + 1
while charindex(substring(@cString, @nEndSubString1, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndSubString >=@nBegSubString -- skip the character in word, if any
select @nEndSubString = @nEndSubString - 1, @nEndSubString1 = @nEndSubString1 - 1
if @nEndSubString >=@nBegSubString
begin
select top 1 @wordcount = WORDNUM from #GETALLWORDSFROMTEXT order by WORDNUM desc
select @cString = substring(@cString, @nBegSubString1, @nEndSubString1-@nBegSubString1+1)
-- we use a function GETALLWORDS which one works with strings of a type nvarchar(4000)
-- we add outcome result in the temporary table
insert into #GETALLWORDSFROMTEXT (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD)
select (@wordcount+WORDNUM), WORD, (@nBegSubString+STARTOFWORD-1), LENGTHOFWORD from dbo.GETALLWORDS(@cString, @cDelimiters)
select @nBegSubString = @nEndSubString + 1, @nEndSubString = @nEndSubString + 4000
end
else
select @nEndSubString = @nEndSubString + 4000 -- In a case if the substring consists of one delimiter
if @flag = 1
break
if @nEndString <= @nEndSubString
select @flag = 1, @nEndSubString = @nEndString
end
-- If in a calling procedure the table was not created, we show the result
if @RetTable = 1
select * from #GETALLWORDSFROMTEXT
end
GO
Example of the call Stored procedure SP_GETALLWORDSFROMTEXT
declare @cDelimiters nvarchar(256)
select @cDelimiters = '"-,.:!?«»()'+SPACE(1)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(12)
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
drop table #GETALLWORDSFROMTEXT
create table #GETALLWORDSFROMTEXT (WORDNUM int, WORD nvarchar(4000), STARTOFWORD int, LENGTHOFWORD smallint)
exec dbo.SP_GETALLWORDSFROMTEXT 'Your Table name', 'Your Id field name', Value of Id field, ' text or ntext field name', @cDelimiters
if object_id( 'tempdb..#GETALLWORDSFROMTEXT') is not null
select * from #GETALLWORDSFROMTEXT
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cString nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 1, @BegOfWord = 1, @flag = 0, @cString = isnull(@cString, ''),
@cDelimiters = isnull(@cDelimiters, nchar(32)+nchar(9)+nchar(10)+nchar(13)), -- if no break string is specified, the function uses spaces, tabs, carriage return and line feed to delimit words.
@nEndString = 1 + datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while 1 > 0
begin
if @k - @BegOfWord > 0
begin
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cString, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
select @wordcount = @wordcount + 1, @BegOfWord = @k
end
if @flag = 1
break
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) > 0 and @nEndString > @k -- skip break characters, if any
select @k = @k + 1, @BegOfWord = @BegOfWord + 1
while charindex(substring(@cString, @k, 1) COLLATE Latin1_General_BIN, @cDelimiters COLLATE Latin1_General_BIN) = 0 and @nEndString > @k -- skip the character in the word
select @k = @k + 1
if @k >= @nEndString
select @flag = 1
end
return
end
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
View 3 Replies
View Related
Sep 9, 2005
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer.
But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet
HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50)
set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
View 11 Replies
View Related
Aug 18, 2005
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
View 1 Replies
View Related
Feb 5, 2008
I have a procedure that executes a sql statement passed in as a parameter. The parameter variable is
@pSqlStatement.
CREATE PROCEDURE [dbo].[ProcedureTest]
@pSqlStatement nvarchar(4000)
AS
BEGIN
DECLARE @retVal int;
BEGIN TRY
EXECUTE sp_executesql @pSqlStatement, N'@ret int OUTPUT', @retVal OUTPUT;
PRINT 'try @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');
END TRY
BEGIN CATCH
PRINT 'catch @retVal = ' + ISNULL(CAST(@retVal AS varchar), 'null');
END CATCH;
END;
Here is an example of this procedure being called:
EXECUTE [dbo].[ProcedureTest]
@pSQLStatement = N'EXECUTE @ret = [log].[Log.SetSeverityLevel] 'debug'
Note that in the sp_executesql statement, the @ret variable from the dynamic SQL gets assigned to the local variable @retVal.
Here's the problem; when the procedure [log].[Log.SetSeverityLevel] that gets dynamically executed does a RAISERROR, the code in [dbo].[ProcedureTest] falls through to the catch block in [dbo].[ProcedureTest] as it should, but the value of @retVal is null in the catch block, when the return code in [log].[Log.SetSeverityLevel] is set to -1.
If you comment out BEGIN TRY / END TRY / BEGIN CATCH / END CATCH in [dbo].[ProcedureTest], the value of @retVal is correctly seen as -1. There's something about the TRY CATCH structure that's blowing away @retVal when an error is raised and it falls through to the CATCH block.
Anybody ever seen this before? Is there a workaround?
Thanks - Dana
View 7 Replies
View Related
May 12, 2005
I am trying to find a way how to query following data from a table that looks like this:
ColA ColB ColC
1 2 3
Output should look like this:
ColA
1
2
3
View 2 Replies
View Related
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
Apr 6, 2015
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
View 3 Replies
View Related
Apr 2, 2002
I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)
Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20
What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?
Any help is greatly appreciated.
thanks,
-scott
View 1 Replies
View Related
Oct 27, 2004
I have a table like:
ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE
I would like output depending on ID to put Disc into two columns. Like:
ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE
Any help will be appreciated. Thanks
ZYT
View 2 Replies
View Related
Apr 24, 2012
I am trying to add two columns together and find all the entries that equal zero I have:
select table1.nbr, table2.itemvalue as CC
from table1, table2
where table1.nbr=table2.nbr and exists (select (OpnAc1+OpnAc2) as Open from table2
I want to take the sum of OpnAc1 and OpnAc2 and find all that equal 0, (meaning no open 1 or open 2)
View 2 Replies
View Related
Feb 23, 2006
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
View 4 Replies
View Related
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
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
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
