Calculating Percentages And Showing In Separate Columns

Aug 31, 2013

SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'

[Code] ....

In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?

1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999

View 3 Replies


ADVERTISEMENT

Calculating Percentages For Each Row

Dec 10, 2007

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask


Thank you in advance for any replies.


Paul

View 5 Replies View Related

Calculating Percentages Based On Multiple Rows

Oct 2, 2014

I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.

Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?

Category Example

IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice

1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00

Product Example

1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99

Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,

[Code] ....

I use Microsoft SQL 2008

View 2 Replies View Related

Add 2 Separate Columns From Separate Tables Using Trigger

Feb 15, 2012

I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...

View 14 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

Jun 27, 2007

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.



-Thanks,

Digs

View 3 Replies View Related

Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table

Jan 19, 2008

Code Block


Hi,

I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:

Table Name: metrics_ladder

id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.

Table Name: metric_details

id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2

Table Name: metric_type_details

id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated

The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:

id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>

Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.

Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,

metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally

metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).

As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:

security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.

which is then unpivoted.

The SQL that achieves this is more or less as follows:

*********
START SQL
*********

declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)


-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;

with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id

*********
END SQL
*********

The result then needs to be unpivoted and stored in metrics_ladder.

And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.

Many thanks (if you've read this far!)

M.

View 6 Replies View Related

Separate Columns

Nov 9, 2005

Hi to all of you,

I am new to SQL I have a problem that I can’t solve.
I have a column with Surname and name (SMITH, James) in one table with data I just need to separate in two columns in one Surname in the other one Name I know how to unite two columns using substrings but not to separate in two columns.

Help is highly appreciated

View 4 Replies View Related

Calculating The Sum Of Columns...

Oct 31, 2006

It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

View 5 Replies View Related

Calculating Difference Between Two Columns

Feb 12, 2014

To calculate how many months are between the current date minus the First_Post_Date

For example
The First_Post_Date is displayed as follows following “25/07/2012”

Current date is 12-02-2014

The difference between the two dates is approx 20 months ..To make the calculation easier, it might be easier to default the day of First_Post_Date to 01 and do the same with the currentdate

So it would be 01/07/12 and 01/02/14

View 3 Replies View Related

Separate A Long String And Put It Into Three Columns

Apr 17, 2015

How to separate a long string and put them into three columns.

For example string

Toronto|Ontario|Canada,Dallas|Texas|USA,New York|New York|USA,Windsor|Ontario|Canada

I have a table with 5 columns, 3 columns are City, State and Country.

I would like to separate them and put those into categories

Continent City State Country Added

Toronto Ontario Canada
Dallas Texas USA
New York New York USA

View 1 Replies View Related

Problem Calculating The Division Of 2 Columns

Apr 20, 2008

Hi
I have 2 tables

table1 which gives the schedule for day
id sat sun mon tue wed thu fri
1 40 50 35 75 80 90 95
2 30 40 45 45 95 85 45
3 40 50 25 25 75 75 55
4 50 60 35 55 65 65 65
5 70 35 75 65 30 35 70

table2 which gives total schedule for week
id week
1 500
2 600
3 550
4 450
5 650


i want to calculate to percentage schedule for each day for week for each value in id column

something like
sat/week where table1.id = table2.id

what im doing is
select (sat/week)
from table1
inner join table2
on table1.id = table2.id
group by id

its giving me error because im using group by clause

what can i do ??
please help

View 3 Replies View Related

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

Determine Value Based On Two Separate Date Columns

Jun 20, 2014

This is the logic I need to incorporate in to sql

if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H

I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.

Is there a way to look at multiple columns in a case statement?

CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end

View 5 Replies View Related

SQL 2012 :: Results To Show Up As Separate Columns

Aug 22, 2014

We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.

SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,

[code]....

View 3 Replies View Related

SQL 2012 :: Put Results Into Separate Columns Based On Value?

Feb 25, 2015

I am trying to take the results of a query and re-orient them into separate columns.

select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD

As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.

I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.

View 9 Replies View Related

Returning Subsets Of The Same Column In Separate Columns...

Mar 23, 2006

I have the following 2 Sql queries. They both are rowcounts of the same column but based on different criteria. What I want to do is return the two results side by side in separate columns:
-- Subscriptions since Sept. 24th
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate is not null
and c.clientunsubscribe = 0
and c.validemailaddr = 1
and s.unsubscribe = 0

-- Subscriptions in February
SELECT count(*)
FROM SiteMemberTable103 s(nolock)
JOIN clientmembertable25 c(nolock) ON s.memberid = c.memberid
WHERE site_firstjoindate BETWEEN '2006-02-01 00:00:00.000' AND '2006-03-01 00:00:00.000'
AND c.clientunsubscribe = 0
AND c.validemailaddr = 1
AND s.unsubscribe = 0

It seems like a UNION ALL should work but it just returns the results in one column. I tried changing the count by specifying a different column for each but that doesn't work either. I also tried writing it as one query and using alias to differentiate the two tables but that just gives me syntax errors. I suspect there is a more elegant way to do this but I'm at a loss. Any help would be greatly appreciated!

Caeanis

View 1 Replies View Related

New View Not Showing Columns

Jul 9, 2001

Any idea why I cannot see (in Enterprise Manager) all the coulmns of a selected table? All I get is * all columns - not each one, meaning I cannot select individual columns in my new view. Could it be the version? I am running SQL 7.0 SP1

Thanks

View 2 Replies View Related

SQL Server 2008 :: Calculating Median Over 2 Columns?

Aug 13, 2015

I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)

Example DB:

Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359

and I need it to display on SELECT as

column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359

Is this possible on 2008 R2?

View 7 Replies View Related

Get Latest Records When Date And Time Are Separate Columns?

Mar 26, 2012

I have 2 tables:

TransactionsImport (which is the destination table)
TransactionsImportDelta

I need to do the following:

Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.

Problem is date & time are in separate columns:

Table structure:

Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600

View 2 Replies View Related

SQL 2012 :: Separate Domain Attribute Into Two Columns In Excel Add-In?

Aug 18, 2014

Can you separate a domain attribute into two columns in the Excel Add-In?

We are using the Excel Add-In as the UI for business users and they want to see one of the domain attributes as 2 different columns.

The attribute is Store, currently it is displayed like this:

"123 {STORENAME }".

They want it displayed in two columns in the Excel document for easier filtering/sorting: Store Number and Store Name.

Is this possible? If one column is changed the other would change right along with it.

View 2 Replies View Related

Data Mining :: Merge Two Or More Columns In Separate Tables

Jul 14, 2015

SQL 2008R2

Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.

What is example of best method to do this ?

View 2 Replies View Related

Matrix Report Not Showing All Columns

Jan 16, 2007

I have a matrix report that I am using with MonthName(Fields!Month.Value) across the top of the report. However, when there is no data under the column, the month is skipped. So, for example, I get this:

January March

Actual Goal Actual Goal

25 25 30 35



I have tried adding

iif(Sum(Fields!Total.Value, "DatasetName")> 0, Sum(Fields!Total.Value, "DatasetName"),"N/A")

But it still is not showing up.

Any ideas?

Thanks,

SHP



View 5 Replies View Related

Upstream Columns Not Showing In Merge

Aug 8, 2007

why wouldn't an upstream column show up in the merge editor?

I can see the column in the metadata by clicking the green arrow going into the merge, but it doesn't not show up as a selectable value in any of the drop downs.

this is true for input1 and input2 in the merge

anyone know how I can force it to show up?

View 4 Replies View Related

SQL Server 2014 :: Splitting Similar Data Into Separate Columns?

Aug 18, 2015

If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip

Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018

The query I am using

select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip

from Loan AS L

LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'

View 3 Replies View Related

Transact SQL :: How To Split Comma Separated Columns Into Separate Rows

Sep 14, 2015

I have values in two columns separated by commas, like shown below:

I need the Output Like

How to do this in SQL server ?

View 6 Replies View Related

Reporting Services :: Calculating Difference Between Columns Within Column Group

Jun 15, 2015

Given the attached report, is there an easy way of calculating the difference between the Today and QTR Start column? Because of the Account Group, the report looks like the sample shown on the second image.

Sample report:

View 4 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

In SSIS, What Is The Best Way To Take A Column With Comma Separated Strings And Separate Them To Multiple Columns

Jul 10, 2006

Hi There,

Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?

for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?

Is Derived column transformation the best way to do it?

Thanks.

Sam.

View 6 Replies View Related

Select Query - Showing Columns Data Row Wise

Dec 28, 2011

I have get 3 columns in a select query. how can show these 3 columns in row wise.

View 2 Replies View Related

Columns Not Showing On Local Host Reporting Services?

Jun 4, 2007

i created a layout in visual studio 2005 with the proper dimensions to print on an Avery 5160 label sheet (3 labels across and 10 down)



in the Body properties, i set Columns to 3, ColumnSpacing to 0.125", and Size 2.625" (W) & 1" (H).



i created a list table with a text box of the same dimension as the Size in the Body.



the thing is when i go to preview the report in visual studio, it just shows all the data but only in one column, but if i click the Print Layout button, it shows all 3 columns, then when i print it, it works! so i assumed that this was just the way visual studio handled the data. no big deal...



the problem is when i upload this to the reporting services on our local intranet and run it from the reporting services, it shows only one column (the left most only) and prints that way too; it's almost like i need a Print Layout button on the reporting services (which there isn't one).



is there something that i need to set so that it will print in 3-column form from the reporting services so people can print on label sheets?



i hope this makes sense. also, i tested this on the same printer just to make sure it wasn't hardware.

View 3 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

Reducing Storage Space By Moving Rarely Required Columns To Separate Table While Sharing Primary Key Constraint

Aug 17, 2006

Dear all,

In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.

During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.

However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.

Hence, I had to create a quite awkward design to enforce the constraints:

1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.

Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.

Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.

Thanks in advance for any advice.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved