DB Design :: Make Dynamic Columns When Duplicated Values Appear
Jul 17, 2015
I'm trying to run the following sql
SELECT DISTINCT
Anvendelseskoder.[Usage Code] AS [Building Code],
Anvendelseskoder.[Usage Code Value] AS [Building Description],
HeleDanmark_DAWA.KVHx
FROM Anvendelseskoder
RIGHT JOIN HeleDanmark_DAWA
[Code] ...
It gives me the following error:
Msg 4104, Level 16, State 1, Line 26
The multi-part identifier "aa.KVHx" could not be bound.
When clicked, it marks the first time i call "aa.KVHx"
Which I do in: "WHERE S2.Nr=2 AND s2.KVHx=aa.KVHx) AS Kode2,"
View 2 Replies
ADVERTISEMENT
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
Feb 14, 2008
I have a table like that:
ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
View 2 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
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
Oct 4, 2006
Hi guys, I have the following store procedure:PROCEDURE dbo.AddSearchColumn (@A1, @A2. @A3, @A4, @A5) ASDeclare @cElements cursor,@DocNum varchar(100)BEGIN Set @cElements = cursor for select FirstNaname rom dbo.DocTable1open @cElements fetch NEXT from @cElements into @DocNumwhile (@@FETCH_STATUS = 0) begin if (@DocNum==@A1 //Here is what I need to do: I want use @A1 at the first loop step, @A2 at the second, and so on....... // @A1, @A2 are the parameters in inputendENDclose @cElementsDEALLOCATE @cElements I hope my explanation is clear.Please, give some hints.Thanks for your time,Christian Malatesti
View 7 Replies
View Related
Dec 31, 2007
Hi,
In my application i need to access mutiple table. I'm writing a stored procedure
in which i need to access tables such as TB01,TB02 .. Basically TBFY
where FY is parameter.
I tried for OPENQUERY, but that needs me to add a linked server, which i don't
think is a good idea.
Can anyone suggest on how can i do so?
I'm using SqlServer2000.
Thanks.
View 6 Replies
View Related
Nov 19, 2007
Hi,
I have a requirement where i need to build the table name dynamically in the following queries.
declare @REF_DATA_TYPE nvarchar(20)
set @REF_DATA_TYPE='COUNTRY'
these are 4 cases where i need to use the table name dynamically
1. IF exists(select 1 from 'MD_REF_'+@REF_DATA_TYPE where code=@code_T)
2. Update 'MD_TB_REF_'+@REF_DATA_TYPE
3. from @ACTUAL_DATA p join 'MD_REF_'+@REF_DATA_TYPE T on T.code=P.code
4. INSERT INTO 'MD_REF_'+@REF_DATA_TYPE(Code,[Name],Description)
But i am getting error when i do this.
Please let me know what to do to solve this
Thanks in advance
View 5 Replies
View Related
May 8, 2012
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
View 7 Replies
View Related
Jul 9, 2006
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
View 1 Replies
View Related
Jun 27, 2007
Hi All,
The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?
Thanks,
Manoj Kumar
View 2 Replies
View Related
Oct 16, 2015
I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
[Code] ....
Table2: TransactionFormula
Only has 2 fields, No & Amount
the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9
Here's the example result for TransactionFormula records
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
View 5 Replies
View Related
May 22, 2015
How to make SSRS report page size dynamic, or in a way where viewer can set it to the size they want?
Set to 0
View 2 Replies
View Related
May 6, 2015
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA : abc,cde,efg
ColumnB : cde,xyz,abc
the result in
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
View 5 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Jun 14, 2001
Please help me, i want to know how to make sql server columns case sensitive for a login table.
View 1 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
Jun 23, 2014
I have the following table records structure:
Code:
drop table ##t1
create table ##t1(col1 int, col2 int, col3 int, col4 int)
insert into ##t1(col1 , col2, col3, col4)
values (1,2,3,4),
(5,6,7,8)
[Code] .....
I can't figure out how to put all nulls at the bottom and have all of my records on the top.
View 11 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Mar 16, 2007
I'm currently working on a project that I'm having some design difficulties with and I was hoping that some of you could help me out. What I'm designing is sort of a webbased directory service based on different custom made templates. Imagine the yellow pages with different column requirements for each branch. Example:
Template: Restaurant
Columns: Name, Adress, TakeAway
Template: DBAForHire
Columns: Name, Adress, YearsExperience
I have designed a few tables that look like this:
DECLARE @templates TABLE
(TemplateID INT IDENTITY(1, 1), TemplateName VARCHAR(50))
DECLARE @attributes TABLE
(AttributeID INT IDENTITY(1, 1), AttributeName VARCHAR(50))
DECLARE @template_attributes TABLE
(TemplateID INT, AttributeID INT)
DECLARE @values TABLE
(ValueID INT IDENTITY(1, 1), TemplateID INT, AttributeID INT, Value VARCHAR(200))
INSERT INTO @templates
SELECT 'Restaurant' UNION ALL SELECT 'DBAForHire'
INSERT INTO @attributes
SELECT 'Name' UNION ALL SELECT 'Adress' UNION ALL
SELECT 'TakeAway' UNION ALL SELECT 'YearsExperience'
INSERT INTO @template_attributes
SELECT 1, 1 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL SELECT 2, 2 UNION ALL SELECT 2, 4
INSERT INTO @values
SELECT 1, 1, 'Lumbagos TexMex' UNION ALL SELECT 1, 2, 'Mainstreet Oslo' UNION ALL
SELECT 1, 3, 'Yes' UNION ALL SELECT 2, 1, 'Peso' UNION ALL
SELECT 2, 2, 'Somewhere IN Sweden' UNION ALL SELECT 2, 4, '10'
SELECT TemplateName, AttributeName, Value
FROM @templates a
INNER JOIN @template_attributes b
ON a.TemplateID = b.TemplateID
INNER JOIN @attributes c
ON b.AttributeID = c.AttributeID
INNER JOIN @values d
ON b.TemplateID = d.TemplateID AND c.AttributeID = d.AttributeIDThe problem I need help with is that with the current design all "Values" are defined as varchar(200)...is it possible to change the design so that it's equally flexible but also fasilitates different datatypes? In some cases I would like to have a description column with way more than 200 characters, and I'd also like to save numbers as actual numbers...not like my example above with "YearsExperience". Is it at all possible?? I would appreciate your insight on this...
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
View 4 Replies
View Related
Aug 20, 2005
Hi
I want to make random record from both columns
Not just like this
"SELECT Firstname,Lasename FROM rndnames ORDER BY NewID()"
but more like this but this code dont work becouse i dont know how to put it into the code:
"SELECT Firstname FROM rndnames ORDER BY NewID()"
"SELECT Lasename FROM rndnames ORDER BY NewID()"
As you see i want both columns to be random placed..
Please help me...
View 1 Replies
View Related
May 18, 2015
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english
Desired output
name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english
View 4 Replies
View Related
May 22, 2008
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
View 7 Replies
View Related
Dec 22, 2006
Hi,
In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.
Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...
Is that possible?
I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...
Thanks for any help,
David
View 1 Replies
View Related
Jul 18, 2007
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
John Smith
Title: MSTP Location: Main Campus IP:127.0.0.1
Softwares:
Adobe Standard 7.0 Access 5.0
Internet Explore 6.0 Office XP
Any suggestion is appreciated.
View 1 Replies
View Related
Mar 22, 2006
hi,
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS.
among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
regards,
nicolas
View 5 Replies
View Related
Dec 13, 2006
Hi all,
I'm a beginner to Report Services, and have tons of questions.
Here's the first one:
if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?
For example,
the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.
Could someone tell me how to do it (create and deploy the reports)?
Thanks a million!
Jonee
View 1 Replies
View Related
Mar 20, 2007
I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.
If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).
I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.
Thanks so much for your help,
Jessica
View 22 Replies
View Related
Sep 4, 2014
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
[Code] ....
View 9 Replies
View Related
Feb 12, 2007
The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.
View 1 Replies
View Related
Jun 10, 2015
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
View 1 Replies
View Related
Mar 24, 2007
I am creating of may of dataflow component.
How make a property with list of predefined values?
Thanks in advance.
View 3 Replies
View Related
Jun 7, 2015
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO
[code]....
View 2 Replies
View Related
