T-SQL (SS2K8) :: How To Select All Or Multiple Rows From Typed XML Variable
Apr 2, 2014
I am using xml schema that is like this:
<DetailRows>
<DetailRow>
<MonthNumber></MonthNumber>
<Amount></Amount>
</DetailRow>
</DetailRows>If my variable contains following xml document as un-typed xml
[Code] ....
However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.
View 1 Replies
ADVERTISEMENT
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
Apr 24, 2014
I have two tables. Table 1 has column "job", table 2 has column "job" and column "item". In table table 2 there are multiple "items" for each "job"
I would like to insert all of the "items" into table 1, based on a join table1.job = table2.job
View 7 Replies
View Related
Jul 2, 2014
I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'
And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned
UPDATE sy01200
SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp')
WHERE Master_Type = 'EMP'
View 2 Replies
View Related
Nov 7, 2014
I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.
But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.
This is a small example of what the rows look like in the resultset, and what I want to single to be.
DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int
[Code] ....
I am not sure how to write the query to have all the data in a single row.
View 2 Replies
View Related
May 14, 2015
I have a table that has for example data
I am looking to write a script that will change the first table into the second table.......
Table 1
Account No Name other field
1 Mr T and Mrs M Lambert xxx
I need to rewrite this as
Table 2
Account No split Name other field
1 a Mr T Lambert xxx
1 b Mrs M Lambert xxx
View 9 Replies
View Related
Apr 3, 2014
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate
The desired results would be:
PERSONDate Department Rate
123404/02/2014 123 80.0
View 7 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
View 7 Replies
View Related
Apr 25, 2014
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
View 9 Replies
View Related
Aug 9, 2014
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
View 8 Replies
View Related
Sep 4, 2014
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2
View 2 Replies
View Related
Dec 2, 2014
I have a job below, which takes the results and send to the users in email.But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.
DECLARE @Body VARCHAR(MAX)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @RowCountINT
DECLARE@idINT
declare@departmentnvarchar(30)
[code]....
View 2 Replies
View Related
Apr 17, 2014
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
Some of the data is:
1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015
but there is a mix of alphabetical characters in there as well.
1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
View 9 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Feb 6, 2015
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
[Code] .....
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
View 4 Replies
View Related
Aug 16, 2006
Hi,
I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!
ex :
DECLARE @MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)
SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?
View 3 Replies
View Related
May 11, 2015
I need to do something like this in SSIS:From one SQL table I need to get some id values, I am using a simple sql query:Select ID from Identifier where value is not null.I've got this result:As a final result I need to generate and set a variable in SSIS with the final value:
@var
= '198','120','ACP','120','PQU'
Which I need to use later in a odbc expression.How can I do this in SSIS?
View 4 Replies
View Related
Jan 26, 2006
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Thanks!
View 2 Replies
View Related
Nov 27, 2014
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
CREATE TABLE #AWP (
[TransDate] [datetime] NULL,
[Description] [varchar](1000) NULL,
[Amount] [float] NULL,
[TotalDueNow] [float] NULL,
[code]....
View 4 Replies
View Related
Aug 5, 2015
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
View 6 Replies
View Related
Feb 27, 2008
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.
stocknumber transtype transsubtype balance
s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100
s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300
I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below
stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400
I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
View 7 Replies
View Related
Apr 24, 2008
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies
View Related
Jul 26, 2013
CREATE TABLE [dbo].[x1](
[nomer] [varchar](15) NOT NULL,
[tgl] [datetime] NOT NULL,
CONSTRAINT [pk_x1] PRIMARY KEY CLUSTERED
(
[nomer] ASC,
[Code] ....
Result :
nomer tanggal
12013-07-28 00:00:00.000
12013-07-29 00:00:00.000
12013-07-30 00:00:00.000
I Want to make :
nomer tanggal
1 28,29,30
View 2 Replies
View Related
Mar 6, 2008
Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:
Table: Profiles
Fields: Id, ProfileName
Table: Groups
Fields: Id, GroupName, ProfileId
I then need to return something like this:
ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"
Does anyone know how this can be done?
Thanks!
View 7 Replies
View Related
Jul 4, 2004
Hi,
I have a table of users, a table of categories, and a many-to-many table linking users to categories.
My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.
Here is a stripped-down version of the table fields:
tbl_User
UserId, Email
tbl_Category
CatId, CatName
tbl_User_Category
UserId, CatId
I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:
DECLARE @list nvarchar(100)
SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId
SELECT @list as List
Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????
Any help would be fantastic!
thanks,
Rob
View 4 Replies
View Related
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
View Related
Jun 5, 2008
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
View 2 Replies
View Related
Apr 29, 2008
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?
View 3 Replies
View Related
Oct 6, 2015
I have a table like this :
ID Description ParentID Level
B.01 Parent 1 H
B.01.01 Parent 1.1 B.01 H
B.01.01.01 Parent 1.1.1 B.01.01 H
B.01.01.01.01 Detail 1 B.01.01.01 D
B.01.01.01.02 Detail 2 B.01.01.01 D
[Code] .....
That means, only select Level=H, and display the last record of H with concatenated the description of each rows delimited with comma.
View 2 Replies
View Related
Apr 7, 2015
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
View 1 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related
