SQL 2012 :: Replacing Column Names With Values
Nov 4, 2015
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.
Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.
View 3 Replies
ADVERTISEMENT
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
View 3 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
Jan 21, 2014
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
View 9 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
May 20, 2014
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
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
Apr 17, 2008
I am building reports against our TFS development db.
One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".
For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:
Ticket ID New Assigned InDev etc
1230001 2 0 0 ...
1230001 0 1 2 ....
SUM 2 1 2 ....
However, I have many different Projects, each of which use their own Status names.
I don't want to duplicate the same basic report, if I can avoid it.
How can I name and generate this data for the unique Status list for each Project?
Simplest analog is: name = First(Fields!Status.Value, "TFSdb")
and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)
However this fails beause:
1. It only delivers the FIRST status value, and,
2. I cannot SUM an expression which is itself an aggregate (using First).
View 4 Replies
View Related
Sep 26, 2007
Hi,
I have a table as follows
Table Master
{
Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}
and the data in the table is as follows
Table Master
{
Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}
I want to retrive column names and its values wheb the ID matches to some value.
Can any one please let me know how to do this?
Thanks alot
~Mohan
View 3 Replies
View Related
Jul 31, 2007
The table tA contains (here I m using storedprocedure to return the values as columns varchar(8000))
ID LocationName
1 Door
2 FrontCounter
The number of records may vary like sometimes it have 2 or 3 or 4 or more records
So I have to return row values as column name.
tAChild
POSID storeID tAID Auth
1 140 1 true
1 140 2 false
Result Should be like ( to create this I m using SP with Dynamic sql query to create temp table)
POSID storeID Door Window
1 140 true true
if more records in tA it should append in the end of result as new column...
Is this the above is right way to implement ?any other ideas about this...
Please suggest me......
View 4 Replies
View Related
Apr 18, 2008
Hi.
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
Thanks
View 2 Replies
View Related
Aug 17, 2015
Is there a performance hit if I use CAPITOL LETTERS (like this) in column names for my SQL Server tables? Logically I would think that lower case letters would have better performance because they are smaller characters and easier to store.
View 4 Replies
View Related
Sep 5, 2004
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
View 4 Replies
View Related
Jan 6, 2015
Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.
View 2 Replies
View Related
Oct 30, 2015
I'm struggeling a bit with this one, goal is to show the first 3 characters of the name and replace the remaining characters with *** stars. Also the number of stars needs to match the lenght of the name as in the example above.I've tried:
REPLACE([ColName],
substring([Colname],
4,
LEN([colname]
)),
'*')
View 5 Replies
View Related
Dec 26, 2013
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
View 4 Replies
View Related
Nov 18, 2005
Hi i have 2 cols
col1 col2
A1 21
A2 22
A3 21
A4 23
I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.
So the report should look like
col1 col2
A1 21
A2 0
A3 21
A4 0
can you please tell me how to do that
Thanks
vic
Vicky
View 10 Replies
View Related
Apr 1, 2004
Hi, I have the following query.
sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"
Status is either 0,1,2 or 3
How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.
So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false
So when the reader reads Expr4 its either true or false.
Is this possible i a query?
View 1 Replies
View Related
Oct 9, 2007
I am trying to replace field values in SSIS. For exapmle, I have 'unkown' and 'N/A', and a few other values that mean the same thing, and I want to give all these fields the same value. I have not been able to find a tool in the toolbox to make this type of change. Is there one? And, if there is, do you know a link to a page explaining its use?
The closest I have come is the Derived column String Transformation:Replace. But, I have not been able to get this to do what I want. If you know of a good reference explaining this tool's use I'd appreciate it.
thanks much,
b2
View 4 Replies
View Related
Nov 28, 2014
I need to round UP values but they should never be rounded down, below is my expected output in RoundVal column.
SELECT 89 AS Val, 100 AS RoundVal UNION ALL
SELECT 329, 1000 UNION ALL
SELECT 6329, 10000 UNION ALL
SELECT 43299, 100000 UNION ALL
SELECT 155329, 1000000
View 1 Replies
View Related
Jul 23, 2007
Hello,
I'm using the query wizard in VB.net to write a query for SQL CE. I want to replace null values with text. I expected the COALESCE function to do this but I get an error message saying its not a valid function. This is a sample.
Select COALESCE(table.Name,'No Name') as Name from table
Any help appreciated
Thanks
View 7 Replies
View Related
Apr 13, 2007
Hi,
I am newbie to reporting services and I need some help. Could any one please let me know how to replace Null value with 0 in the data section of the Matrix. I don't want blanks to be displayed on the report, I want those to be replaced with 0's.
Thanks for your time.
View 4 Replies
View Related
Oct 14, 2014
With the data example below I am trying to consolidate the duplicate rows by flattening the dealer and billcode, or putting those values in each of the columns instead of creating separate rows...
74 MARTHA PATNE RIPLEY 1 23,327,76 ROTTINGDAM AAC SPRINGFIELD 3052 USA MPATRIP@AMERICANALARM.COM,MPATRIP@COMCAST.NET,PRIPLEY@ONECOMMUNICATIONS.COM
[table]
arnumbercustomernamedealerbillcodeaddl_addraddr1addr2branchcityzipcodecountryemaildefaultEmail
39SUSAN THALKER 2271BOTTOMWOOD RDAACHAMMOND02180-2703USASUWS3@COMCAST.NET1
56ANN REBELLO 123SHERIDAN AVEAACMEDFORD2155USANULLNULL
58DARRELL/PATTI SANDERS 6020DOTY AVENUEAACDANVERS1923USANULLNULL
74MARTHA PATNE RIPLEY 123ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@AMERICANALARM.COM1
74MARTHA PATNE RIPLEY 1327ROTTINGDAM DRAACSPRINGFIELD3052USAMPATRIP@COMCAST.NET1
74MARTHA PATNE RIPLEY 176ROTTINGDAM DRAACSPRINGFIELD3052USAPRIPLEY@ONECOMMUNICATIONS.COM1
[/table]
View 1 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Apr 10, 2008
Hi all,
I have run into a problem that I am stuck on. I have 3 tables lets call them a,b, and c. What I want to do is left outer join a with b on a common value, then replace any null values in this result with a default value, and then left outer join table c on the previously joined table (on the columns that I just replaced certain values on).
please help!
Thanks
View 4 Replies
View Related
Aug 12, 2014
I have the following objective:
1. I want to check a column to see if there are values (to Eliminate dups)
2. Once checked the values in a column, if not found insert the new value
Here is my code for this:
ALTER TRIGGER DUPLICATES ON AMGR_User_Fields_Tbl
-- When inserting or updating
AFTER INSERT, UPDATE AS
-- Declare the variables
DECLARE @AN varchar(200)
[Code] ....
View 1 Replies
View Related
Oct 29, 2014
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
View 2 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
Jun 26, 2015
I am trying to add a where condition on an ID column(type - INT) with values coming from a variable (type - STRING). i am using cast to cast the ID as Varchar and then apply the condition, but i am not getting any results back. following is an example of what i am trying to do.using temp table in the example , so you can copy the t-sql and run as is.
CREATE TABLE #TABLE1(ID INT)
INSERT INTO #TABLE1 VALUES (1), (2) , (3) , (4)
DECLARE @ID varchar(8000) = '2,4'
[code]....
View 4 Replies
View Related
Nov 2, 2015
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch.
4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
View 4 Replies
View Related
Feb 6, 2003
Hi,
Im having a couple of problems with SQL.
I have this query...
SELECT Band.Name, Member.Name
FROM Member
JOIN MemberOf ON Member.Mid = MemberOf.Mid
JOIN Band On MemberOf.Bid = Band.Bid
WHERE MemberOf.Instrument = 'keyboards';
which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".
so... my questions are :)
how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.
PS. The three tables are
Band. Which has Bid as a primary key.
Member. Which has Mid as a primary key.
MemberOf. Which links these through its two foreign keys Mid and Bid.
Thanks for your help! (assuming someone does)
View 2 Replies
View Related
Dec 19, 2014
So I have the following column named String in a table:
<key>Children</key><integer>2</integer>
This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?
declare @children int
set @children = 4
I want to do something like this:
SELECT <key>Children</key><integer>@children</integer>
View 4 Replies
View Related
