SQL Server 2012 :: Display Columns With No Data

Dec 12, 2013

Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.

Table schematics

di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int

[Code] .....

View 2 Replies


ADVERTISEMENT

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

SQL Server 2012 :: Display Data In Blocks (Column Name)

Jan 29, 2014

I want to display data in block. Below is the Create,Insert Script and format of desired output.

CREATE TABLE [dbo].[Test_AA](
[CounterpartID] [varchar](2000) NULL,
[CounterpartName] [varchar](2000) NULL,
[SATName] [varchar](2000) NULL,
[NameOfBO] [varchar](2000) NULL,

[Code] ....

Result Should be like This :

ID:17388
Name:Scottie Berwick
SATName:Scottie Berwick
NameOfBO:
SATAccountNumber:

[Code] ....

View 4 Replies View Related

SQL Server 2012 :: Splitting Data Between Two Columns

Dec 15, 2014

Here's the resultset:

Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2

Expected result:

P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A

What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).

View 3 Replies View Related

Don't Want To Display Sensitive Data In Columns

Oct 21, 2014

I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are

Firstname varchar(50) not null
Latname varchar(50) not null
dob datetime not null
addressline1 varchar(50) null
adressline2 varchar(50) null
city varchar(50) null
postalcode varchar(20) null

I want to display null values as nulls empty as empty not null as numbers.

View 4 Replies View Related

How To Display 2 Columns Of Data In 1 Column?

Aug 16, 2007


How to display 2 columns of Data in 1 column?

If I use like this

Select Names, 'myData' from emp

The output is:
Name1 myData
Name2 myData
Name3 myData

But, I need to display like this

Name1
myData
Name2
myData
Name3
myData

please advise

View 5 Replies View Related

SQL Server 2012 :: Compare Two Different Columns Of 2 Different Rows In A Data Set?

Jan 29, 2014

Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.

For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.

RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0

View 4 Replies View Related

SQL Server 2012 :: Get Multiples Rows And Columns Data Into 1 Single Row?

Oct 28, 2015

I have the following results:

CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110

And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.

View 9 Replies View Related

Retrieve Columns And Display Count For Two Different Data Sources

Jan 19, 2012

I am using Query Writer (should be SQL 2005) and have included the following code.

The end result: -should retrieve columns and display the count for two different data sources that were added by personnel in a specific department.

Problem: results are returned but not accurate. The code below works just fine and returns the results for all spot buy orders added by personnel in the sales department. However, I want to add a column in the same report that also counts blanket orders from the exact same table added by personnel in the sales department. The database uses views (v) in lieu of dbo.

Parameters:
@Add_Date_From SMALLDATETIME='',
@Add_Date_To SMALLDATETIME='',
@Last_name VARCHAR(50)='',
@First_Name VARCHAR(50)=''

Query:
SELECT
T1.Last_name,
T1.First_name,
T2.Position,
T3.Add_date,
COUNT(T4.PO_Type) AS 'Spot Buy Added'

[Code] ....

If I substitute COUNT(T4.PO_Type) AS 'Spot Buy Added' with COUNT(T4.PO_Type) AS 'Blanket Added' I also get accurate results for the blanket order. IE separately they work just fine. If I try to combine the two that is where the trouble begins.

What am I doing incorrectly when I try to add the criteria/code for the additional column to count the blanket orders?

View 9 Replies View Related

Columns Display Format While Converting Data Into Excel And PDF

Nov 11, 2007

Hi,

I'm using SSRS to generate reports. i have many columns data to be displayed.while converting the data into Excel and PDF the data, header, and footer are not displaying proper format. what are all the properties to be set for that.

Could any one help in this regard.

Thanks for your help..

View 1 Replies View Related

SQL Server 2012 :: Dynamically Create A Script Only Selecting Columns Where There Is Data?

Dec 2, 2013

I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:

If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.

View 1 Replies View Related

SQL Server 2012 :: Query All Columns In A Database That Have The Data Type As Integer?

Feb 20, 2014

Is there a way to query all the columns in a database that have the data type as Integer.

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

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

SQL Server 2012 :: Calculate Elapsed Time And Display As Row In Results

Jun 19, 2015

I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.

min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'

Here is the table DDL

CREATE DATABASE SpecTest;
USE SpecTest
GO

CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,

[Code] ....

Here is some dummy data to use

INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)

[Code] ....

Example desired results (I hope the formatting works)

Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59

View 9 Replies View Related

SQL Server 2012 :: SSRS - Display A Message When User Enter Wrong Parameter?

Mar 12, 2015

Issue #1 I have a report that takes 8 character parameter, A error message needs to popped out if user enters parameter less than 8 character.

Issue #2 I have a report with Tablix. A message needs to display if Tablix returns no Rows.

View 1 Replies View Related

SQL Server 2012 :: SELECT Query - Cursor To Display Result In Single Transaction

May 25, 2015

Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.

CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

SQL Server 2012 :: Running Total On 2 Columns

Jul 25, 2014

Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.

create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Rows To Columns Conversion

Aug 28, 2014

I would like to reach out in converting rows to cols.

Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate

I have this query. the values of each are under status.

SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status

FROM

((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Encrypted Records In All Columns

Nov 6, 2014

I have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...

Is there any software or any tool which will provide encrypted records of database?

View 1 Replies View Related

SQL Server 2012 :: List Dates In Columns

Nov 11, 2014

I have a table (Event_Table) like:

EmployeeID, CustomerID, Date
1, 11, 2014-11-11
2, 13, 2014-12-10
1, 11, 2014-12-21
2, 13, 2015-01-11
1, 11, 2015-03-02

And now I would like to have a summary with a unique Employee/Customer combination and 3 Date columns like:

EmployeeID, CustomerID, Date1, Date2, Date3
1, 11, 2014-11-11, 2014-12-21, 2015-03-02
2, 13, 2014-12-10, 2015-01-11

Dates should be arranged with the first date in Date1, the next in Date2 and the third in Date3 (if there are forth and more dates I don´t care)

View 2 Replies View Related

SQL Server 2012 :: Adding New Columns To A Table

Apr 28, 2015

I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.

However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?

Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, why one location is better than another location?

View 3 Replies View Related

SQL Server 2012 :: Primary Key And Identity Columns

May 23, 2015

Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.

View 9 Replies View Related

SQL Server 2012 :: Query With Multiple Columns - How To Get Next Value One After Another

Aug 5, 2015

I want to know if it is possible to do the following;

I have patients that may have been transferred to different locations(see below)

location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02

i know i can do a min max to get my first and last values. I want to label the columns something like

1st location, 2nd location, 3rd location, 4th location, discharge location.

there could be 1 location or 20.

is there a way to do this?

i can do a temporary table and then an update query to add the values to those columns.

just not sure how to get the next value and then the next etc.

View 9 Replies View Related

SQL Server 2012 :: Calculate Total Between Two Columns

Sep 14, 2015

I have update schema and I upload image with my desire result.

CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int

[Code] .....

View 7 Replies View Related

SQL Server 2012 :: Grouping Columns In Table

Sep 15, 2015

I have table like below.

filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y

I Need a output like this

filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2

I wanted to group the col1 , col2, col3 and group it as same group.

View 3 Replies View Related

How To Retrieve And Display Data From SQL-server?

Feb 15, 2008

Hello,

I need to retrieve data from a SQL-server table using a stored procedure. I want to retrieve the values from the table and add them to labels on a form. What is the easiest way to do this? Is dataset the solution?

I appreciate any help!


string sCN = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection sqlConnection2 = new SqlConnection(sCN);





SqlCommand cmd = new SqlCommand();

cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@userID", userID);



Int32 rowsAffected;

cmd.CommandText = "get_user"; //Stored procedure to get user data. Takes UserID as parameter

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlConnection2;



sqlConnection2.Open();

rowsAffected = cmd.ExecuteNonQuery();

sqlConnection2.Close();

View 20 Replies View Related

Row Values Display In Columns

Jun 5, 2015

One Table have 2 Columns.

1.Order_number
2.PSON NUmber

In Table Contains 6 rows with 2 order_numbers and different PSON Number.I want Order Number,PSON1,PSON2,PSON3,PSON4 and its Values

Ex:--Order_number    PSON NUmber
             1                    ONT10
             1                    ONT11
              1                   ONT12
              2                   ONT13
               2                   ONT14

I want   data :-      Order_number    PSON1          PSON2        PSON3
                                  1                     ONT10      ONT11       ONT12
                                   2                      ONT13     ONT14       Null

View 3 Replies View Related

SQL Server 2012 :: Inserting New Columns Based On Condition

Feb 25, 2014

I have a very simple query like the following…

SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)

From this query, its returning me a result set of something like below:

--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------

RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER

Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…

Something for example (I am not trying to write a condition: my question is how to write it),

if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.

Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…

See the attachment for better formatting...

---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH

View 7 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 :: Define Primary Key Based On 2 Columns?

Sep 24, 2014

I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.

View 0 Replies View Related







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