How To Assign Distinct Data Values For Attribute
Nov 17, 2015I need the Rate to be of a value from 1-5 to indicate the stars for Rating attribute in my database.
View 2 Replies
ADVERTISEMENT
Select Distinct Column Data With Other Values From The Table
Dec 16, 2004I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
Distinct Values Among Distinct Column Values
Jan 9, 2015Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
Set A Attribute To Random Values In A Given List
Jul 20, 2005Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing
View 1 Replies View RelatedT-SQL (SS2K8) :: Assign Next Available Column Values?
Sep 29, 2014find below object and data:
create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails
Output of above is display as:
CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5
Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.
CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5
How To Assign Identity Key Values In Replication
Sep 28, 2006This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
How To Assign Values To A Variable From A Xls Sheet?
Sep 28, 2006I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?
select Proyecto from [Carga$]
TIA,
Transact SQL :: Recursive Query To Assign Values
Jul 30, 2015I'm trying to find it difficult to use recursice CTEs or better solution for a special request below. There are two tables 1) @sizes serves as a lookup or reference for right drive and 2) @test is sample data with different sizes. Assume that I want to evenly distribute the drive letters from table1 to table2 by checking the size available.
E.g.: for the first record in @test; id = 1 where the size is 50 and it fits in Y: drive -- left over space in Y: = 50
id=2, size 2.5, space available from left over = 50 - 2.5 = 47.5 which again fits into Y:
id = 3, size 51, cannot in fit in Y: drive as there is enough space in Y: to allocate (51 > 47.5)
so pick the next drive check on availability again
DECLARE @sizes TABLE (id TINYINT, size DECIMAL(5,2), drive VARCHAR(3))
INSERT INTO @Sizes
SELECT 1,100.00,'Y:'
UNION ALL
SELECT 2,80.85,'Z:'
[Code] ....
-- My output should look like
col1 , val , path
A 50 Y:
B 2.5 Y:
C 51 Z:
D 2.6 Y:
E 52 Z:
F 2.7 Y:
Analysis :: Best Way To Add Additional Parent Child Attribute Values
Apr 16, 2015I have a parent Child attribute in my dimension. I am currently displaying the correct ID value as the business wants. So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName) for this ID.However they do not want it concatenated. They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this.
My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID
How To Assign Dataset Values In Header In RDLC Report
Apr 25, 2008I am using RDLC report with Microsoft visual studio 2005. In the first page of rdlc i have two text boxes and one table in body section. In the second and subsequent pages i want to repeat the data from textbox1 and textbox2 along with table data continuation of page1.
Currently the continuation of table data from page1 to page2 is working properly. But the textbox1 and textbox2 data also needs to be repeated in every pages.
I tried the following steps, but fails to work.
1. added two text boxes in header section and another two text boxes in Body section.
2. Assigns the dataset value to textboxes in body section.
(Ex: =first(Fields!Address.Value)
3. Assigns the textboxes value from Body section to the corresponding text boxes in header section.
(Ex : =first(ReportItems!textbox1.Value))
Result:
The header text box value displayed in the first page only and not repeated in the subsequent pages.
Expected:
Whatever assigned to the header section should be repeated in the subsequent pages. But only page number, date... is reflecting in other pages and not the text box values in header section.
Kindly give me the solution.
Thanks in advance.
Integration Services :: Assign Values To Variables Dynamically In 2012 SSIS Package
Jul 16, 2015Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List
Sep 7, 2015We 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.
SQL Server 2014 :: How To Check Existing Attribute In Insert Attribute In XML Field
Aug 10, 2015I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
Measure Group Attribute Key Column Does Not Match Source Attribute
May 16, 2008
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
Distinct Values
Nov 17, 2006Hi, i hope i'm asking this on the right forum.
My problem is:
I need to select only distinct columns from a table, for this i can use: "select distinct username from table_user" but this is not working each time .... someone told me that i can use "alias" ... what does this means and how can i use alias for geting only distinct usernames from my table???
hope that is here someone that can help me ... !
10x
David Palmer SQL Manager
DISTINCT Values From A Table
May 4, 2007Hi,I am trying to output a list of data from a table, showing only one record of each TypeID.So, for instance, I have a simple SQL query that says:SELECT DISTINCT AlbumTypeID FROM Album
ORDER BY AlbumTypeID DESCThis works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESCThe output is completely wrong.Many thanks
How To Calculate Sum For Distinct Values In MDX
Jul 27, 2007Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_IdMrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
Brijesh
Grouping On Distinct Values
Aug 30, 2013I want to get the summation of the amount column against all the distinct values of Description fields. May be the "Where" (applied on Tdate Column) Clause unable to contains all the Unique(Distinct) values from the single Column (Description).
Table Structure :
CREATE TABLE [dbo].[TransLine](
[TransID] [int] NULL,
[No] [int] NULL,
[Description] [varchar](50) NULL,
[Code] ....
Sample data : [URL] ....
Select Distinct Values
Jun 25, 2014I have a problem when selecting distinct values from a table. I want to select those distinct numbers with a code that I want. To make it more clear i`ll show it on an example.I have the following table:
tel_no | code | id_res
+-----------+--------------+-----------
0742062141 | a-not answer | 1
0742062141 | a-not answer | 2
0742062141 | c-answer | 3
0754231456 | a-not answer | 4
0754231456 | a-not answer | 5
When I use the following query it returns the distinct number but with the first code it finds:
select a.* from test_funnel as a
inner join
( select distinct tel_no,MIN(id_res) as id_res
from test_funnel
[code]....
Help In Coalescing Distinct Values
Sep 21, 2007Hi all,
I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]
Code Snippet
CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO
CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO
INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO
INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO
DECLARE @listSkills nvarchar(max)
SELECT DISTINCT @listSkills= COALESCE(@listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @listSkills=(SELECT Skill = @listSkills)
SELECT @listSkills
DROP TABLE #Lkp_SkillCategory,#Tbl_Request
Getting Distinct Values On One Column ?
Sep 11, 2007We have a query in which there are 20,000 rows and 90 distinct ID's
If we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8
from #TEMPX
we get the 90 distinct values, but if we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts1avg, ts2avg, ts3avg, ts4avg, ts5avg, ts6avg, ts7avg, ts8avg from #TEMPX
TS1 contains 90 distinct values, whily ts1avg has 2,000 disitinct rows
Is there a way to get Distinct to work against only one column, i.e. SiteID ?
Return DISTINCT Values
Aug 22, 2007Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
How To Insert Distinct Values In Database
Apr 11, 2008Hello,
I have three columns in my database named FirstName, LastName, StudentID. i want to insert only the distinct values and avoid the values which are already in the database. can anybody please help me with the logic? is there any possibility of checking for the redudant values in the stored procedure?
below is my code:protected void Page_Load(object sender, EventArgs e)
{Label1.Visible = false;GridView1.Visible = false;
}protected void Button1_Click(object sender, EventArgs e)
{
loaddata();TextBox1.Visible = false;
TextBox2.Visible = false;TextBox3.Visible = false;
Button1.Visible = false;Label1.Visible = true;
Label2.Visible = false;Label3.Visible = false;Label4.Visible = false;
loadGrid();GridView1.Visible = true;
}private void loaddata()
{string str = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection conn = new SqlConnection(str);SqlCommand cmd = new SqlCommand("Sp_Student", conn);
conn.Open();cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text;cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = TextBox2.Text;cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = TextBox3.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
private void loadGrid()
{string strn = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection connect = new SqlConnection(strn);string select = "select * from Student";
SqlCommand comm = new SqlCommand(select, connect);SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();da.Fill(ds, "Student");GridView1.DataSource = ds.Tables["Student"];
GridView1.DataBind();
}
Thanks
Sandeep
Distinct Values Problem With SP5a
Aug 10, 1999Hi,
Has anyone encountered this?
After applying SP 5a I found out that my SQL strings with sums and groups produced double rows. I found out that grouping one column (type CHAR(6)) produced two distinct rows of the equal values.
All the values in that column have 4 characters, but it seemed like some distinct values were grouped by the whole field length i.e 6 characters. I haven't seen earlier that those blanks after field value change the grouping results.
When I transferred the table and data to old SQL Server without any SPs, query showed distinct values correctly.
The sort order and other settings are the same. Can anyone tell where to look for help?
TIA, Al
Distinct Values And Join Query
Feb 24, 2015SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
Distinct Values In Select Statements
Jul 6, 2006Hello,
I have a table, which contains well over 30'000 rows. One of the Columns is for ransaction type, of which there are about 20-25 different types.
I am trying to pull a list of each type value, using a select distinct TSQL command...but thus far am unsuccessful.
Has anyone had a similar situation?? Am I wrong in using a Select Distinct?? Many thanks to all who reply!
How To Conditionally Count Distinct Values?
Mar 17, 2008Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.
departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...
I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.
My report would produce the following output considering the above dataset:
Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2
Class 1 2
Barcode 123 2
Barcode 789 1
Department 2 0
Class 7 0
Barcode 456 0
I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.
DB Design :: How To Get Distinct Count Of Values
Sep 21, 2015I have the below query,
select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column
[Code] ....
o/p: is :
LastEnforcementState NumberOfComputers PComputers
Compliant 7056 91.54
Downloading update(s) 39 0.51
[code]....
I want to add these two rows information into single row,
Failed to Download Updates(s) 131(127+8) 1.66(1.65+0.1)
how to do this in my sql query?
Return Distinct Values From Stored Procedures
Aug 17, 2006I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies View RelatedDistinct Record Equal To 2 Values From Same Column
Dec 6, 2013Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:
name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1
Select Distinct On Field With Values Seperated By
Apr 5, 2006I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.
If I run the following command.
select distinct(DSCODES) from DB
The following is returned.
S100,S102,S103
S100,S103,S105
What I would like returned is the following
S100
S102
S103
S105
Is this possible?
Thank you in advance
Graham
How To Insert Only Distinct Values From A Flat File
Sep 11, 2007
I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.
Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.
Can We Create Distinct Values For Document Map On A Particular Level?
Apr 16, 2007Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about document map on SQL Server 2005 Reporting Services. I found one problem with the values retrived for the document level. That is, the values are not distinct, they are duplicate for the document level. Is it possible for us to get the distinct values for a particular document level? Hope my question is clear for your help.
Thank you very much in advance for your help and advices. I am looking forward to hearing from you.
With best regards,
Yours sincerely,