Transact SQL :: Merge 2 Rows Into One Line Output

May 21, 2015

How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.

CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,

[Code] ....

Is it possible to return the data in the following format merging 2 lines of data into one output:

SiteID   ReadStartDate ReadEndDate    ReadStartIncome     ReadEndIncome
L0020     19/05/2015 05:00 20/05/2015 05:00    85.98     145.98
L0101     19/05/2015 22:07    20/05/2015 22:14         1,936.08       1,438.89
L0102     20/05/2015 21:16   19/05/2015 21:48   143.65 243.5

I am using SQL 2008 R2.

View 12 Replies

Merge Join - No Output Rows

Apr 24, 2008

Hi,

I have a problem with a Merge Join providing no output (when it should have 1890 rows). My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination. I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below). I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below). Then the two remaining legs use a Merge to get my destination output (see Level 3 below).

I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation. The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows. Both Merge Joins are identical. The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in. Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join), but C2 baffles me with 0 rows of output (when it too should have 1890). I receive no Ouput errors and the execution completes showing all green.

Level 1
Multicast (output A1, A2) [667 rows]
Multicast (ouput B1, B2) [1890 rows]

Level 2
Merge Join (input A1, B1) --> (output C1) [1890 rows]
Merge Join (input A2, B2) --> (output C2) [0 rows]

Level 3
Merge (input C1, C2) --> (output D1) [1890 rows]*

I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck. Any help/ideas would be appreciated.

Thanks,

Devin

* Should be 3780 rows

View 4 Replies View Related

Transact SQL :: MERGE Function And OUTPUT To A File

Sep 14, 2015

I used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.

What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.

View 5 Replies View Related

Merge Allmost Identical Rows For Output Table

Dec 20, 2007

I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.

View 3 Replies View Related

Merge Join: Nr Of Output Rows Unchanged When Amount Of Input Changes

May 25, 2007

Dear all,

I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.

Situation as follows.

The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.

If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.

When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.

Any help will be greatly appreciated.

Kind regards,

Albert.

View 4 Replies View Related

Transact SQL :: How To Merge Two Rows Into One Row

Sep 24, 2015

I have a table that has columns like following

trans_code
gl_code
12qw3
a1235
12qw3
a6578
12qw3
t531
e34g6
gt25
e34g6
y7327
e34g6
v23409

Is there an easy way to turn this into format below ??

trans_code
gl_code_1
gl_code_2
12qw3
a1235
a6578
12qw3
a1235

[code]....

View 5 Replies View Related

Integration Services :: Merge Join Transformation - No Output Rows Redux

Aug 4, 2009

I am using SSIS in SQL Server Enterprise 2005. I have two OLE DB data sources from two disparate databases (IBM DB2 and Microsoft SQL Server), some columns from each of which are to be included in the merged output results. I have noted the various requirements in the forum postings with regard to sorting the OLE DB sources and specifying the output source columns as being sorted, as well as the requirement that the join fields in the two sources be close/exact matches. Yet, when I run this in VS, while the work area reflects the expected number of rows being input into the Merge Join transformation, no count is reflected as output from that transformation into the final destination table.Specifically, my two data sources (IBM DB2 and MS SQL) are configured as follows:

IBM DB2 contains an SQL statement that uses Cast operations to create the result columns.and an ORDER BY clause to ensure that the output is sorted by the desired two columns.. The OLE DB source property setting for IsSorted is set to true; the Output Columns folder column definitions for "key_ source_dtsy" and "key_source_dtrt" have their SortKeyPosition properties set to 1 and 2, respectively. Those field are both defined as data type DT_STR, with lengths of 4 and 2, respectively. Below is the Path metadata from the Data Flow Path editor from the path from this source:

IBM DB2 source"Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source
Component""ID_CODE" "DT_STR" "0" "0" "10" "1252" "0" "" "Source F0005 User Defined Codes""CODE_DESCR_1" "DT_STR" "0" "0" "30" "1252" "0" "" "Source F0005 User Defined Codes""CODE_DESCR_2" "DT_STR" "0" "0" "30" "1252" "0" "" "Source F0005 User Defined Codes""key_source_dtsy" "DT_STR" "0" "0" "4" "1252" "1" "" "Source F0005 User Defined Codes""key_source_dtrt" "DT_STR" "0" "0" "2" "1252" "2" "" "Source F0005

User Defined Codes:

MS SQL contains an SQL statement that takes the columns as they are in the MS SQL table (no Cast operations needed); it also uses an ORDER BY clause to ensure the output is sorted by the join columns. The OLE DB source property setting for IsSorted is set to true; the Output Columns folder columns for "key_source_dtsy" and "key_source_dtrt" have their SortKeyPosition properties set to 1 and 2, respectively. Those field are both defined as data type DT_STR, with lengths of 4 and 2, respectively. Below is the Path metadata from the Data Flow Path editor from the path from this source:

MS SQL source"Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source Component""id_code_name" "DT_I2" "0" "0" "0" "0" "0" "" "Source CodeName in db dwVdFY""key_source_dtsy" "DT_STR" "0" "0" "4" "1252" "1" "" "Source CodeName in db dwVdFY""key_source_dtrt" "DT_STR" "0" "0" "2" "1252" "2" "" "Source CodeName in db dwVdFY"

The Merge Join transformation specifies an INNER JOIN using the columns named "key_source_dtsy" and "key_source_dtrt" from the respective data sources.I know there are alternative ways of accomplishing my intent (Lookup, port MS SQL table to IBM DB2 so join can occur in SELECT statement, etc.; however, I'd like to use this functionality and assume that it should work.

View 13 Replies View Related

Transact SQL :: Merge Multiple Rows Onto A Single Row

May 13, 2015

I have a set of data which contains individual logon and logoff data. The table is as follows:

AGENTID, EVENTTIME, CURRENTSTATE
1234, 2015-05-12, 15 (For Logon) or 25 (For Logoff)

I'm hoping to extract this data as follows:

AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION

View 19 Replies View Related

Transact SQL :: Merge Two Rows Into One Based On Same Customer Number

Oct 26, 2015

I have a table Customer with below column

CustomerNumber , FName,LName,DOB

I have either 2 or 1 row for each customer number I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.

example:
CustNumber Fanme   Lname   DOB
1 Sam tiller 08/26/1981
1 Joe   timy 01/02/1986
2 jack niks   09/09/1990

Result I want:
CustNumber Fname1 Lname1 DOB1 Fname2 Lname2 DOB2
1 Sam Tiller 08/26/1981 Joe timy 01/02/1986
2 Jack niks 09/09/1990 null null null

View 7 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

DataReader Output Skipping First Line

Jan 20, 2007

I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.Any help would be greatly appreciated.============================================1 reader = cmd.ExecuteReader()
2 If Not reader.Read() Then
3 Return False
4 Else
5 For i = 0 To reader.FieldCount - 1
6 strLine += reader.GetName(i).ToString & Chr(9)
7 Next
8 objStreamWriter.WriteLine(strLine)
9 strLine = ""
10 With reader
11 While .Read
12 For x = 0 To .FieldCount - 1
13 strLine = strLine & reader.GetValue(x) & Chr(9)
14 Next
15 objStreamWriter.WriteLine(strLine)
16 strLine = ""
17 End While
18 End With
19 End If

View 2 Replies View Related

Output Parameter Or 1 Line Recordset?

Feb 8, 2004

I was nosing around the documentation of the IBuySpy store and noticed this line:

---------------------------------------------------------

This stored procedure accepts a ProductID as an input parameter and returns product information to the caller in the form of output parameters. A one line recordset is not used in order to gain a performance increase. It is used primarily on the product details screen.

----------------------------------------------------------

Can anyone shed some light on if this is a best practice when retrieving a single row from the db? Is the performance gain very noticable? I mean it makes sense and all, I just see a lot of examples using a datareader when there is more than 1 column, and ExecuteScalar() when there is one.

I just wrote an application that might benefit from this if its worth the trouble of changing a couple methods in my data layer calls to ouput parameters.

Thanks in advance for any input!

View 7 Replies View Related

SQL Server 2012 :: How To Go To Next Line In Output And Print A Table

Aug 13, 2014

I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:

This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'

Also how can I create a table in this variable, something like this:

(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME

(Course Name) (Last Completed) (Now due in Month/year)

My T-SQL code:

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)

[Code] ......

View 2 Replies View Related

Command Line Parameters Are Invalid Is The Output For A SQL Job To Run SSIS Package

May 25, 2006

Hello,

I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.

The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn��t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:

/SQL "Package" /SERVER FTLQSQL03 /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager��s connection string is blank, compared to other connection managers having related values.

Does anyone know how to make the job working?

Thanks in advance

I

View 3 Replies View Related

Merge Join Output Bug?

Nov 7, 2006

I've run into something that looks like a bug to me but I wanted to run it by the board:

Merge join 2 sorted tables.

Table1: ColumnA : Sort Order 1, ColumnB Sort Order 2

Table2 : ColumnA: Sort Order 1, ColumnB Sort Order 2, ColumnC not sorted

Merge Join the two tables on ColumnA and ColumnB...

Choose the following as output columns

A + B + C = works

C = works

A + C = works

B + C = NOT work.. error message: The column with the SortKeyPosition value of 0 is not valid. It should be 2.

Basically if you choose one or more of the sorted columns in the output at least one of them has to be the column with Sort position 1 or you'll get that error.

Is this a bug or intentional? If you do not have sort column 1 in the output that output could no longer be considered sorted... so perhaps the error is related to that (instead of error I'd expect some warning about the sorting). Interesting that it lets you choose C only becuase that also makes the output unsorted.

View 1 Replies View Related

Output To Fixed Width Flat File Not Adding Line Breaks

May 19, 2008

Hi All,

I have a simple SSIS package that runs a query on the db and outputs a fixed width flat file. I have all my column widths defined and in the connection manager i can preview the output. Everything looks great. All the fields fall where they should and each record is on it's own line.

When i run the SSIS program and then go open my text file with a text editor the ouput is all on the same line. I have tried changing my file format from fixed width to ragging right and adding a row delimiter but that doesn't work either. I feel like i'm missing something small here. It could even be an issue w/ my text editor (although i've tried to open the text file in multiple editors). In the flat file connection manager I have my file defined to be 187 characters long, So figure every 187 characters it should output a new line (it should add the carraige return right?).

Has anyone encountered an issue like this?

Any help would be much appreciated.

View 4 Replies View Related

SQL 2012 :: Merge Two Queries To Have Only 1 Output?

Nov 2, 2015

We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.

Query 1:

set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))

[code].....

Again, the only difference is the Table the info is coming from...

View 3 Replies View Related

Merge Join - Output 0 Records

Apr 14, 2008

I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.

But when I use SSIS merge join to join the two tables my output is 0 records.

I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.

however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?

View 5 Replies View Related

SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output

Aug 20, 2015

Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.

Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)

Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0

What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0

I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.

View 6 Replies View Related

One Line For 3 Rows

Mar 27, 2007

for MS SQL 2000
I am having :

Table1

ID | N1 | N2 | N3 |
-------------------
12 | A | | |
12 | | B| |
12 | | | C|

how can I get :

12,A,B,C ?

thank you for helping

View 6 Replies View Related

Line Number In Transact SQL

Feb 16, 2004

Hi,

How do you generate line numbers in Transact SQL?

Example:

In Sybase, I could use this...
select number(*), customer_name from customer_table

number(*) customer_name
----------------------------------------------------
1 Customer Name 1
2 Customer Name 2
3 Customer Name 3

Thanks,
Geoff

View 2 Replies View Related

Merge A PDF File And SQL Reporting Services Output

Aug 6, 2006

Does anyone know how i can go about merging preexisting pdf files and SQL server reporting services output. Can this be done in reporting services? For example, I have 5 pages from a pdf files which is created from another 3rd party software provider. I then i have output from sql reporting services. How can i merge these two outputs and deliver it over .Net/ ASP framework?

thanks

View 4 Replies View Related

How To Merge Report Output Into Single File?

Mar 14, 2008

Hi

I have read a few other posts about how to merge multiple report output files into a single document e.g. a single pdf.
There are a few approaches:
1) Generate post script files and then merge and oprint via a post script driver.
2) Generate seperate pdf files and then merge them into single document using a custom library.

I have a third idea and would appreciate any input:

Dynamically generate a RDL file that contains sub-reports, one for each report required in the final document, publish and run this as the final report. This could happen way before actually running the report i.e. the user has a tool where they select reports for a pack, tool then generates new RDL file and publishes it to sql reporting services, gets run at some later point in time.

Some challenges:
-Generate a table of contents with page numbering?
-Layout of sub-reports, not sure how they would be rendered across multiple pages?
-Managing parametes across sub-reports at run time.

Any suggestions or ideas?

Thanks

T

View 1 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related

How To Get All Same Rows In Single Line

Mar 6, 2013

i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :

BEGIN TRY
SET NOCOUNT ON
DECLARE @l_numberOfRecordsInserted INT
-- Check if valid Application User ID is passed
IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )

[code]....

so last column section name should come as comma seperated .

View 3 Replies View Related

Multi Line Rows

Jul 10, 2006

Using Visual basic 2005

I have a form and set up my datasource, bindings etc.

All works well, adding, updating, and deleting via form to sqlexpress except.....

I have 2 columns set up in sqlserver that get data from muliline textboxes on my form.

Column 1 "Material" is nvarchar(50)

Column 2 "Quantity" is int

The first multine textbox "Material" works fine, eather updating in the textbox or in the Datagridview cell.

the second multiline textbox "Quantity" works when I just put in a number in the first line of the multiline textbox, when I put in the 2nd number on the second line, nothing gets updated.

I I do that in the datagridview cell (Shift-Enter), I get an error.

"System string was not in correct format".

I tried integer.parse, but that dosn't work. I'm assuming it has something to do with the next line ascii code or something like that.

Can anyone out there help with this

View 3 Replies View Related

Merge Join - Output Of Lookup As Sorted Field?

Nov 3, 2007

I'm doing a data conversion with one of my fields (SUMDWK) from one of the tables that will be used in a merge join. With the new, converted field, I do a look up. From this look up, I want to take a new field FiscalWeekOfYear, and replace the original field, SUMDWK. This is necessary because SUMDWK is one of the sorted fields. In the look up, it is not possible to change the Output Alias. Does anybody know a way around this? Thanks.

View 14 Replies View Related

Merge Input Output Selection Dialog Is Hanging

Oct 26, 2006

Hello

I'm trying to use the Merge component. When i attach a datasource to the the component, the Select Input/Output dialog box should popup.. It does, but VS.NET is hanging and i can only shutdown the procesess...

Any idea how i should solve this? how can i re-register this component?

ps. sql 2005 sp1 is installed.

Thanks
Marco

View 4 Replies View Related

Transact SQL :: Syntax To Group Items In One Line?

Nov 20, 2015

I have records that I get in this format:

ID                      Customer               Type                 TypeNUm
100                      Tiger                    Item                   T100
100                      Tiger                     Item                   T200
100                      Tiger                     Item                   T300
100                      Tiger                     Shiper                  SAAA
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                   WE
200                       Panera                  PO                   POBBB

The reftypes are not always the same, what I need is to get it in this form

ID                      Customer               Type              TypeNUm
100                      Tiger                     Item                   T100,T200, T300
100                      Tiger                     Shiper                  SAAA
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                      WE
200                       Panera                  PO                     POBBB

View 6 Replies View Related

Integration Services :: Merge Inner Join Gives Different Output Based On Sort Key?

Sep 23, 2015

In the first image as can be seens i have 2 different data sources and then they are being joined using "Merge Inner Join". The "sort" is on BusinessEntityID column of Person table and "Sort1" is on "PersonID" of Customer table. The merge join of these 2 result in 19,119 rows.

On the other hand, if i use single data source and use a query with inner join on tables used in the first image (ie. 2 tables being used in 2 different data sources) as depicted in second image. Also, since merge cannot operate without SortKey i have defined TerritoryID as sort key in the advanced editor. The number of rows i get after this is "10,274". My select query was :

SELECT
P.BusinessEntityID,
P.PersonType,
P.Title,
P.FirstName,
P.MiddleName,
P.LastName,
P.Suffix,
C.TerritoryID
FROM stg.Person AS P
INNER JOIN stg.Customer AS C ON C.CustomerID = P.BusinessEntityID
ORDER BY C.TerritoryID;

According to me, it should have been the same as in first case i am using merge inner join and in second case i am using SELECT query with inner join. Upon drilling down i found that in the first case , my sort keys are BusinessEntityID and PersonID, if i modify this to CustomerID and BusinessEntityID as this is my join condition (in ithe inner join query shown above), i get the desired output. What i was wondering was, how the sort order change the Join Condition?

View 3 Replies View Related

Transact SQL :: Group By On A Transaction At Line Item Level?

May 20, 2015

I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.

Here is the code:

SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.Company

[code]....

Due to case by statement and having multiple category I have to use multiple group by.

select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase

[code]....

View 5 Replies View Related

Transact SQL :: Convert Multiple Lines With Different Values In Value Columns In 1 Line

Dec 1, 2015

I have this query:

SELECT
          ID1,
         ID2,
          type,
         (case when type = '1' then sum(value) else '0' end) as Value1,
          (case when type = '3' then sum(value) else '0' end) as Value2,
          (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y'
GROUP BY ID1, ID2, Type

Which returns:
ID1    ID2        Type     Value1     Value2     Value3
005    11547    0          0.00         0.00       279.23
005    11547    1          15.23       0.00        0.00
005    11547    3          0.00         245.50    0.00

And I want to obtain this result:
ID1    ID2        Value1     Value2     Value3
005    11547     15.23       245.50    279.23

View 5 Replies View Related