SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies

SQL Server 2008 :: Merge Multiple Rows Of Same ID Into Single Row

Feb 19, 2015

I need the requirements of merging multiple rows of same ID as single row.

My Table Data:

IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL

Required Output Should be,

IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH

How to achieve this output. Tried grouping but its not working also producing the same result.

View 1 Replies View Related

SQL Server 2008 :: How To Pivot Unknown Number Of Rows To Columns Using Data As Column Headers

Sep 10, 2015

I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.

I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?

Below is the DDL to create the table and populate it.

USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,

[Code]....

View 4 Replies View Related

SQL Server 2008 :: MERGE Statement - Cannot Filter Set Of Rows In Target Table

Feb 10, 2015

I have a table:

declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)

A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).

I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.

If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:

merge ( select * from tableName where id = 10 ) as target
using ...

and I can't have:

merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...

This means I cannot filter the set of rows in the target table to a handful of records where id = 10.

View 1 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 1 Replies View Related

Power Pivot :: Extracting MAX From A Table Based On Certain Conditions

Nov 20, 2015

I want to calculate the target based on Flag value if Flag value is "Y" ....than MAX(Customer Target) else MAX(SLA target).Flag column contains "Y" , "N" and some blank values . Flag, Customer Target and SLA target are the columns in Table1. I have used the below formulas

Target:=IF('Table1'[ Flag]= "Y",MAX('Table1'[Customer Target]),MAX('Table1'[SLA Target]))
Target:=IFERROR(IF('Table1'[Flag]= "Y",MAX('Table1'[Customer Target]),MAX('KPI'[SLA Target])),BLANK())

View 7 Replies View Related

Power Pivot :: DAX Formula For Extracting A Column Value From Table Based On Certain Conditions

Jul 20, 2015

I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.

Here is the scenario:

I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).

I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).

Is this possible using DAX ?

View 6 Replies View Related

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

Oct 13, 2015

Can I force the following measure to be visible for all rows in a pivot table?

Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)

FYI, I also have other measures as well in the pivot table that I don't want to affect.

View 3 Replies View Related

SQL Server 2008 :: Pivot Data In Table?

Jun 24, 2015

View 1 Replies View Related

SQL Server 2008 :: Dynamic Conversion Of Pivot Columns

Jun 15, 2015

I have attached SQL File which Gives me the below resultset Excel.xlsx

But the problem is i am not able to round off the dynamic columns in side my PIVOT, how to rewrite the dynamic query.

View 4 Replies View Related

SQL Server 2008 :: Pivot Function - Additional Final Column

Mar 11, 2015

I've managed to use the pivot function using the code below which gives me the following output:

Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2

This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.

[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf

[Code] ....

View 7 Replies View Related

SQL Server 2008 :: Dynamic Query Pivot Values Change

Aug 6, 2015

I have below script

CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20

[Code] ....

-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180

****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows

ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid

View 2 Replies View Related

SQL Server 2008 :: MERGE And Delete From Target

Mar 12, 2015

I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.

My current code is

sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,

[Code] ....

The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.

My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.

Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.

If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like

NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.

That's quite a bit of work my end to do that tho so I'd like to be sure it works.

View 1 Replies View Related

SQL Server 2008 :: Merge 2 Select Statements

Aug 25, 2015

SELECT
part.num, woitem.qtytarget AS woitemqty,

(SELECT LIST(wo.num, ',')

FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID

[Code] ...

View 5 Replies View Related

SQL Server 2008 :: Merge Replication - Replacing A Subscriber

Mar 4, 2015

We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.

The original server is an ancient Win2003 86x

The Server team will build a new Win2008r2 64x, and use the same name and IP address

And I'm tasked with the SQL part.

I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.

How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?

View 1 Replies View Related

URGENT Plz ! Pivot Rows To Columns In SQL Server 2000

May 16, 2007

Hi,

I have the following data in a table called "Products" :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L

I need to group this data like this :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L

How can I do that ???

Thanks !

View 3 Replies View Related

SQL Server 2012 :: How To Pivot Column To Rows Within A Group

Dec 18, 2014

I need to convert the column to rows but within group. example

Group Name Value
p a 1
p b 2
p c 3
p d 4
q a 5
q b 6
q d 7
r a 8
r b 9
r c 10
r d 11

This need to be transposed to :

Group a b c d
p1234
q56NULL7
r891011

View 3 Replies View Related

SQL Server 2012 :: Pivot Rows And Columns In The Same Query?

Mar 26, 2015

I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.

The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.

View 2 Replies View Related

SQL Server 2012 :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,

[Code] ....

Desired result:

Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200

View 6 Replies View Related

How To Find Conditions Across Rows (attendance)

May 4, 2006

Hello,I need to find students that have 4 consecutive absences. When astudent is absent 4 times in a row, they can be dropped from the class.My class attendance file contains each attendance by date and whetherthey were present or not. When the student has 4 consecutive value 1(absent) for a given session and a given class the are considered to bedropped.If I needed to know the total number of absences, I know I could groupand summarize, but this one has the consecutive twist.Table:CREATE TABLE "dbo"."clsatt"("FULL_CLASS_ID" CHAR(15) NOT NULL,"STUDENT_ID" CHAR(20) NULL,"SESSION_ID" CHAR(10) NULL,"MEETING" SMALLINT NOT NULL,"PRESENT" CHAR(2) NOT NULL);Present value of 1 is absent, value of 2 is present (3 means holiday)Classes typically meet 12 times.I would want something likeFULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'as the output.Notice in the example the first student was absent the last 4 meetingsThe second student 5 absensesand the third student was totally absentIn these three examples, they are flagged as dropped.TIARobInserts:---------------------------------------------------------------------------------insert into clsatt values ('BUS100','1675812194','200203',1,'2')insert into clsatt values ('BUS100','1675812194','200203',2,'2')insert into clsatt values ('BUS100','1675812194','200203',3,'2')insert into clsatt values ('BUS100','1675812194','200203',4,'2')insert into clsatt values ('BUS100','1675812194','200203',5,'2')insert into clsatt values ('BUS100','1675812194','200203',6,'2')insert into clsatt values ('BUS100','1675812194','200203',7,'2')insert into clsatt values ('BUS100','1675812194','200203',8,'2')insert into clsatt values ('BUS100','1675812194','200203',9,'1')insert into clsatt values ('BUS100','1675812194','200203',10,'1')insert into clsatt values ('BUS100','1675812194','200203',11,'1')insert into clsatt values ('BUS100','1675812194','200203',12,'1')insert into clsatt values ('BUS100','1712400537','200203',1,'2')insert into clsatt values ('BUS100','1712400537','200203',2,'2')insert into clsatt values ('BUS100','1712400537','200203',3,'2')insert into clsatt values ('BUS100','1712400537','200203',4,'2')insert into clsatt values ('BUS100','1712400537','200203',5,'2')insert into clsatt values ('BUS100','1712400537','200203',6,'2')insert into clsatt values ('BUS100','1712400537','200203',7,'2')insert into clsatt values ('BUS100','1712400537','200203',8,'1')insert into clsatt values ('BUS100','1712400537','200203',9,'1')insert into clsatt values ('BUS100','1712400537','200203',10,'1')insert into clsatt values ('BUS100','1712400537','200203',11,'1')insert into clsatt values ('BUS100','1712400537','200203',12,'1')insert into clsatt values ('BUS100','1801704805','200203',1,'1')insert into clsatt values ('BUS100','1801704805','200203',2,'1')insert into clsatt values ('BUS100','1801704805','200203',3,'1')insert into clsatt values ('BUS100','1801704805','200203',4,'1')insert into clsatt values ('BUS100','1801704805','200203',5,'1')insert into clsatt values ('BUS100','1801704805','200203',6,'1')insert into clsatt values ('BUS100','1801704805','200203',7,'1')insert into clsatt values ('BUS100','1801704805','200203',8,'1')insert into clsatt values ('BUS100','1801704805','200203',9,'1')insert into clsatt values ('BUS100','1801704805','200203',10,'1')insert into clsatt values ('BUS100','1801704805','200203',11,'1')insert into clsatt values ('BUS100','1801704805','200203',12,'1')insert into clsatt values ('BUS100','1922287588','200203',1,'1')insert into clsatt values ('BUS100','1922287588','200203',2,'1')insert into clsatt values ('BUS100','1922287588','200203',3,'2')insert into clsatt values ('BUS100','1922287588','200203',4,'2')insert into clsatt values ('BUS100','1922287588','200203',5,'2')insert into clsatt values ('BUS100','1922287588','200203',6,'2')insert into clsatt values ('BUS100','1922287588','200203',7,'2')insert into clsatt values ('BUS100','1922287588','200203',8,'2')insert into clsatt values ('BUS100','1922287588','200203',9,'2')insert into clsatt values ('BUS100','1922287588','200203',10,'2')insert into clsatt values ('BUS100','1922287588','200203',11,'1')insert into clsatt values ('BUS100','1922287588','200203',12,'2')insert into clsatt values ('BUS100','2188469657','200203',1,'1')insert into clsatt values ('BUS100','2188469657','200203',2,'1')insert into clsatt values ('BUS100','2188469657','200203',3,'2')insert into clsatt values ('BUS100','2188469657','200203',4,'2')insert into clsatt values ('BUS100','2188469657','200203',5,'2')insert into clsatt values ('BUS100','2188469657','200203',6,'2')insert into clsatt values ('BUS100','2188469657','200203',7,'2')insert into clsatt values ('BUS100','2188469657','200203',8,'2')insert into clsatt values ('BUS100','2188469657','200203',9,'1')insert into clsatt values ('BUS100','2188469657','200203',10,'1')insert into clsatt values ('BUS100','2188469657','200203',11,'1')insert into clsatt values ('BUS100','2188469657','200203',12,'2')insert into clsatt values ('BUS100','2515197431','200203',1,'1')insert into clsatt values ('BUS100','2515197431','200203',2,'1')insert into clsatt values ('BUS100','2515197431','200203',3,'2')insert into clsatt values ('BUS100','2515197431','200203',4,'2')insert into clsatt values ('BUS100','2515197431','200203',5,'1')insert into clsatt values ('BUS100','2515197431','200203',6,'2')insert into clsatt values ('BUS100','2515197431','200203',7,'2')insert into clsatt values ('BUS100','2515197431','200203',8,'1')insert into clsatt values ('BUS100','2515197431','200203',9,'2')insert into clsatt values ('BUS100','2515197431','200203',10,'2')insert into clsatt values ('BUS100','2515197431','200203',11,'1')insert into clsatt values ('BUS100','2515197431','200203',12,'2')

View 6 Replies View Related

SQL Server 2008 :: Collation Used By SSIS MERGE JOIN Task

Aug 27, 2012

Can the collation used by SSIS be changed or influenced during install or run time? We have found that our databases, that use a mandatory "LATIN1_GENERAL_BIN", have incorrect SSIS Merge Join output. Changing our database collation in testing didn't make a difference. What matters is the data. Which Windows collation is SSIS using?

Example Data:
FIRSTNAME
FIRSTNAME
FIRSTS-A-NAME
FIRSTS_A_NAME
FIRST_NAME
FIRST_NAME
FIRSTname
FIRSTname
FIRS_NAME

put in a Sort task before the Merge Join task as setting advanced properties isn't enough (as described by Eric Johnson here --> [URL] ......

We are using 64-bit SQL Server 2008 R2 w/ SP1 in Windows Server 2008 R2 ENT w/ SP1.

UPDATE from ETL team: Explicitly ordering the source with "COLLATE Latin1_General_CS_AS" seems to have the same effect as using a separate sort task. We don't feel that we can rely on our findings, however, unless we have documentation that this collation is what is behind SSIS.

View 2 Replies View Related

SQL Server 2008 :: Merge Moves Data Into Wrong Filegroup?

Feb 18, 2015

I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.

This is my starting point:

boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209

After I switch partition 1 to a staging table I run:

ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;

and

ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');

I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:

PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210

How do I make sure that the data stays in AdtLog_Archive_201209 file group?

View 1 Replies View Related

SQL Server 2008 :: Changing Alt Snapshot Folder For Merge Replication?

Sep 22, 2015

I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.

I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?

My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?

View 1 Replies View Related

SQL Server 2008 :: Merge Replication - Publisher And Subscriber On Same Computer?

Nov 6, 2015

For study effect, can I configure merge replication using just one SQL Server Instance?

View 3 Replies View Related

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

View 4 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

SQL Server 2008 :: Merge Statement When Source Table Supplies Duplicates

Apr 26, 2015

With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.

DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,

[code]....

View 0 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

SQL Server 2012 :: Merge Multiple Rows Into One?

Nov 26, 2014

basically i have data like this

order_key comment
1 A
1 B
1 C
2 B
2 D

the data intends to be like this

order_key comment
1 A,B,C
2 B,D

View 3 Replies View Related

SQL Server 2012 :: How To Merge Multiple Rows Into One Row

Dec 24, 2014

I have a table that looks like this ...

idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222

I want to merge the data to look like this ...

idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222

Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.

This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.

Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).

Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?

CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)

-- Insert the records for type 1

INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'

-- Insert the records for type 2, if the id does not exist for type 1

INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'

-- if the id has both type 1 and 2, update the phone2 column with the data from type 2

UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts

View 2 Replies View Related

Select Rows Based On Multiple Date Conditions

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

SQL Server 2008 :: Replicating Merge Range And File Drops For Partitioned Table?

Jul 28, 2015

I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.

Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.

There the following article options:

Copy file group associations
Copy table partitioning schemes
Copy index partitioning schemes

I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.

I could not find and option to copy partition functions.

View 0 Replies View Related