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


ADVERTISEMENT

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

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

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

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

SQL Server 2012 :: Results To Show Differences In Items Purchased Versus Sold?

Jun 3, 2014

I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.

Use tempdb
go
create table #ItemLedgerEntry
(
ENTRYNO INT NOT NULL
, ITEMNO VARCHAR (50) NOT NULL
, POSTINGDATE DATETIME NOT NULL
, ENTRYTYPE INT NOT NULL

[code]....

I know something like this will give me results but I'd like to run one query.

select itemno, count(*) from #ItemLedgerEntry where entrytype = 0
group by itemno
order by count(*) desc

View 5 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

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

SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns

Jun 19, 2014

I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'

[Code] ....

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.

This is my query without the columns I need:

SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: View Results Not Displaying Nil Columns

Sep 5, 2014

I have a view I've created which displays client sortname, partner and date added which displays 7 results.

When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.

Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?

Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN

[Code] ....

In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...

View 8 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 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

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

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

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

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 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

Need Show 2 Results From Same Column In Same Row

Oct 29, 2007

I am trying to build a query for a report and I need both results for a column to show on the same row. The results are multiple names tied to the same account.
This is what I am trying.

SELECT
number AS 'Customer'
(case when seq = '0' then name end) as 'name1',
(case when seq = '1' then name end) as 'name2'
FROM customer
WHERE number between '600080' and '600230'

View 5 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

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

Create DB View To Show Results

Sep 10, 2007

I have 2 Table
Authors
ID Name
1 Clint
2 Voke

Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2

Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.

How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.

thanks,

View 15 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

SQL 2000 View VS. Sproc Show Different Results

May 9, 2005

I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT      *FROM          VMRawUsageWHERE      (ACCOUNT = @VMBox) AND  (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND  (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results.  The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?

View 1 Replies View Related

Query Results To Only Show ONE Result Per Locale

Feb 5, 2015

My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?

Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]

[Code] ....

View 1 Replies View Related

Queries With Different Statements - Show Results In Datagridview

Oct 19, 2015

I have made a couple of queries I want to use into a Visual studio project, Now is my problem:

All my queries have different statements. I believe the best is to show the results in a Datagridview

How to view them there? I know how to do it... but I have got about 30 queries

Here is my code so far:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"

[Code] ....

View 2 Replies View Related

3 Results From One Field - Show Levels In Right Order

Nov 19, 2007


Hi there

We have a web application (database) that uses one field called Application and another called TicketType.

When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3

Eg TicketType, TicketType2, TicketType3

The extra two levels not being compulsory.

I am using sql server 2005 // Reporting Services

My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber



End result looks like this:





Application

App 2

App 3

TicketType

Type 2

Type 3


Software

Internal Apps

proACT





SW Other






Office Issues





General




Application

Click Track server



Alert (App)

Service




Network

Other





Network Fault


Software

Internal Apps

Other



User Account

New




Hardware

Network





HW Fault




Application

Click Track server



Alert (App)

Disk space






Office Issues





General






proACT



Configuration

Deployment


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Software

Server Software

SharePoint



SW Fault

App Failure (Function)


Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.

Can someone help please.


Please ask if I haven't explained myself.

thanks
Dianne

View 9 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

Write Query To Show Results In DB View -Advanced-

Sep 10, 2007

I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1           1        Book1  10 2           1        Boo21  12 3           2        Book3  6 4           1        Book4  13 5           1        Book5  2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
 
 

View 2 Replies View Related

Looping Query Results - Show All Duplicate Records

Feb 4, 2015

Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..

SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR

View 5 Replies View Related

URGENT:How To Show A Appended String To The Query Results?

Oct 31, 2007

Hello Frds,

This is my query


Select Assessment_Id,Question_Id,Question_short_description,

(isnull(Answer_description,' ') + ' ' + Answer_text) as Answer

from dbo.viewX where assessment_id ='xxxx'


Pleae Note: This query is assigned to a string and passed to the database layer. So, i need the modifications with in this query.
Answer_description and Answer_text are the two different fields,
i need to embed a ":" (colon) between the two data if Answer_text is having any data
else display only answer_description.

Thanks in advance..
Phani.

View 17 Replies View Related

SQL 2012 :: Tables On Separate File Groups

Jul 30, 2014

We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..

View 9 Replies View Related

SQL Server 2012 :: Using CTE To Aggregate Separate Tables?

Aug 20, 2014

I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.

My end result should look like the below example.

Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0

So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.

Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.

--create the temp table
Create table #SalesLine
(
Novarchar (50) not null
, LocationCodevarchar (50) not null
, QtyCommitint not null
)
create table #ItemLedgerEntry

[code]....

I am close to the desired results but can't find a way.

View 9 Replies View Related







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