Hi.
I have a table with 100s of rows.
I want to create a new column that will always have only one
row for each user (either yes, or no).
How do i do this?
thanks.
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
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.Â
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedI'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View Related
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!!!
Good afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi,
I have got a table where i want to display sum of count(Column1), count(Column2) in another column.How can this be done?
for example
SELECT SUM(Count(pxInsName)+Count(pxFlowName)) AS "pySummaryCount(1)" , Count(pxInsName) AS "pySummaryCount(2)" , Count(pxFlowName) AS "pySummaryCount(3)" , pxAssignedOrg AS "pxAssignedOrg" , pxAssignedOrgDiv AS "pxAssignedOrgDiv" , pxAssignedOrgUnit AS "pxAssignedOrgUnit" FROM pc_assign_worklist WHERE pxObjClass = ? GROUP BY pxAssignedOrg , pxAssignedOrgDiv , pxAssignedOrgUnit ORDER BY 'pySummaryCount(1)' DESC
But sum function can not be used on aggregate function.
Is there any other way.
Hi Folks, Im new to SQL, and I am trying to do the following:
I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.
I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.
I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:
DECLARE @List varchar(100)
SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1
SELECT @List
I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?
Thanks
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View RelatedI have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Bitmask fields! I am capturing row changes manually via a high frequency ETL task. Â It works effectively however i am capturing the movement of multiple fields. Â A simple example, for Order lines, i have a price, a discount and a date. Â I am capturing a 001, 010, 100 respectively for each change. Â
I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed).Â
Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â
1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â
          InsCode value 1002 should always mapped to 1006
           InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
[Code] ....
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Say my dataset has a column with numbers 1-15. Those numbers are attached to other information.
CUSTID Name
1 Jim
2 Pat
3 Mary
4 Sally
5 Jane
How can I pull only even customer IDs?
Will something like this work?
SELECT * FROM TABLENAME
WHERE CustomerID/2 = 'Even/Int'
Hi,
I want to have the sum of the values of a column .. however the range is not definate.. a.. i.e we need to check till the last rec.. and need to get the sum
pls help
Hi,
I have a status column in Excel file which is getting data from checkboxes.
When I exported my file to Excel this status column is containing 2 or more values.
The possible values for status are:
1 active
2 Pending
3 Inactive
So for each case it might be possible to get values like
case 1: status: Active Pending
Case 2 : Status: Inactive pending
So in my Excel file is containing these values in one status column.
I am thinking of storing statuses in a separate table like this:
Case Active Pending inactive
1 1 1 0
2 0 1 1
In the SSIS transformation how to achive this? how to split the status columns values and where to store?
Thanks
We have a column that contains values only between 0 and 1; most of them are 0 or nearly 0. We perform a basic clustering on that single column with the data mining add-in for excel. The results show three clusters: two of them centered in values between 0 and 1 (0 and 0,47), the other one looks strange and has a very high negative value which is not present in our data. I haven€™t tried running the same clustering directly on Visual Studio. This hapend with other algortim. Do you know the problem We have screen dump if it needs Best regards Capgemini - Oslo
View 4 Replies View RelatedI'm building a textbox with search functionality towards SQL 2000.
The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column.
The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better.
Whats is the best way of doing this?
Cheers!
/Eskil
Hi,i m using vwd2005 express and sql express.i've created a table in sql express and set id fied to primary key and set identity to yes.now when i add a record a the id is auto generated this way 1,2,3,4....but when i delete these records and add the new record.the id column now adds a new id as 5 then 6,7 etc.but i want the id to generate from 1,2,3 again..how can this be done?any idea?thanks. jack.
View 6 Replies View RelatedI have a form with a few panels on it, and the visibility of the panels is determined by a radiobutton selection. Once a user selects a radio button item, the appropriate panels visibility changes to true. On two of the panels, there is a drop down list with a list of clients, and the dropdownlists are named Client1 and Client2 (Client 1 is included in panel 1 and client 2 is in panel 2)
How do I insert the values for client1 or client2 (depending upon which is visible) into the same sql column via a stored procedure? The database field is called client. How do I get the value from my vb.net form into the stored procedure?
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
View 8 Replies View RelatedI have two tables.
S
SNO CHAR(5)
SNAME CHAR(20)
S1
SNo CHAR(5)
SNAME CHAR(20)
The structure is same but S(SNAME) does not contain data. If I use the following command of SQL then It appends the rows in table. I want that the values should be inserted from TOP to BOTTOM.
INSERT INTO S (SNAME)
Select sname from s1
Could anybody solve this probelm?
Thanks
Hi all-
I'm inserting 4-digit codes into my db table where the column is a smallint type. Some of these codes begin with 0's, like "0003". How can I format the val in this column to always have the extra 0's in front?
Thanks!
bm
View 3 Replies View RelatedI have a column that is frequently updated with an append query. I need to turn all of the incoming positive values into negative values (as well as convert all of the existing). I tried to multiply the column by -1 in the formula option in table design view but couldn't find the proper formula format.
Can anyone suggest an approach.
I have 2 tables which have the data as follows:
A:
Id Reason Amount Subject RecordNo
1 Gift 100 first 11
2 Gift Reason 200 second 12
3 Gift Reason 100 first 11
The result that I want is :
Reason Amount Subject
ALL 200 first
i used the following query
select
case when Grouping(B.Subject) <> '' then 'ALL' else B.Subject End as Subject ,
case when Grouping(B.reason) <> '' then 'ALL' else B.Reason END as Reason,
SUM(B.amount) as amount
from B
where B.RecordNo = 11
group by grouping sets (B.Reason,B.Subject)
but it's giving me 3 rows in return.
I want only one summation row. How can i get that?