Changing The Format For A SQL Server Query

Apr 3, 2006

Hi,

I have to generate a daily report of survey answers by users? My question is there a way to reformat the query so it generates a table or report with it showing the rows as columns instead.

Here is my initial query.

SELECT

dbo.Reporting_SurveyAnswers.DateCreated AS DateCreated

,dbo.Reporting_SurveyAnswers.questionid AS QuestionID

,dbo.Reporting_SurveyAnswers.surveyid AS SurveyID

,dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

,dbo.Reporting_SurveyAnswers.userid AS UserID

,dbo.Reporting_User.LastName1 AS LastName

,dbo.Reporting_User.FirstName AS FirstName

,dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

,dbo.Reporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


FROM

dbo.Reporting_Surveys

INNER JOIN dbo.Reporting_SurveyQuestions

ON dbo.Reporting_Surveys.surveyid = dbo.Reporting_SurveyQuestions.surveyid

INNER JOIN dbo.Reporting_SurveyAnswers

ON dbo.Reporting_SurveyQuestions.QuestionID = dbo.Reporting_SurveyAnswers.QuestionID

INNER JOIN dbo.uvwReporting_User

ON dbo.Reporting_SurveyAnswers.userid = dbo.uvReporting_User.userid

WHERE

dbo.uvReporting_SurveyAnswers.surveyid = 1125

Order by dbo.Reporting_SurveyAnswers.DateCreated

,dbo.Reporting_SurveyQuestions.ordernumber

Select

dbo.Reporting_SurveyQuestions.ordernumber AS OrderNumber

, dbo dbo.Reporting_SurveyQuestions.QuestionText AS QuestionText

To complicate matters, some of the users did not answer some of the questions and some of the questions are duplicated in the rows because the database assigned them one answer each.

Example. Question 18 says "Name all the industries you have worked in. Check all that apply.

What happens is lets say the user checks 4 different boxes. In the query results, it will show 4 rows with question 18 with each answer they checked off.

Any help would be appreciated.

Thanks

The Accidental Tourist

View 3 Replies


ADVERTISEMENT

Changing The Number Format In SQL Query

Jan 30, 2008

Hi


I have query

SELECT col1, a.Inv_Amount as Amount
FROM SPS_Oustandings a

I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator

i need output as 12.345,67
My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid.

Please Help,
Thanks

View 6 Replies View Related

Changing SQL Server Date Format

Jul 19, 2002

SQL 2k w2k server sp2

When I first installed the SQL server on my server, the default date
format was mm/dd/yy. Now I need to change that to dd/mm/yy in
regional settings, but its still not reflected in the DB's in SQL (its
still mm/dd/yy).


Is it possible to change it in SQL without a complete reinstallation
of the server?

View 1 Replies View Related

Changing Table Format

Mar 11, 2004

Hi there,

Its probably easier to draw this problem than describe it, so here goes:

I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])

A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41

I need to convert the table into a more sensible format, like this:

(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)


........ 01/03/2004....01/04/2004
A..........30..................11
B..........28..................09
C..........24..................41


I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?

Thanks very much

Sam

View 4 Replies View Related

Changing Date Value Format

Dec 6, 2013

Aim – Currently i have column “[LAST-STATUS-CHG]” in the following date format “042312” i need this date format to be changed into “23-04-12”.

Also i would like an additional column created called “Start of month” this column should look at “[LAST-STATUS-CHG]” and what ever the day is in the defauly it to 01

Current results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHG
87800000088416042312
87800000088513011212
87800000088612100712

Desired results
FDMSAccountNoACCOUNT-STATUSLAST-STATUS-CHGStart_of_month
8780000008841623-04-1201-04-12
8780000008851301-12-1201-12-12
8780000008861210-07-1201-07-12

My query is
SELECT [FDMSAccountNo]
,[ACCOUNT-STATUS],
[LAST-STATUS-CHG]
FROM [FDMS].[dbo].[stg_LMPAB501]
where FDMSAccountNo = '878000000884'

View 2 Replies View Related

Date Format Changing In SQL SMS

Nov 27, 2007

Howdy,

I live in Australia and use the date format of - dd/mm/yyy, but when i do a query in SQL Management it seems to want its inputs in US format mm/dd/yyy.

For example it errors when i do a search and one of the dates is 27/11/2007, ie there is no 27th "US" Month.

I have checked my local settings (regional and language in control panel), and it all points to Australian formats, it just seems the SMS wants the dates in US.

Any Ideas?



Dwayne Schaffarz

View 4 Replies View Related

Changing The Format In Flatfile

Jan 28, 2008



Hi,

Iam migration data from a table into a comma delimited flatfile,but i need to specify all the columns within [ " ] in the flatfile

for example

i have a column [Name] the values are John,Mani,Raghu.....

The flat file should be outputted as
"John"
"Mani"
"Raghu"

Is there anyway to do this.Pls help.

Thanks,
SVGP

View 3 Replies View Related

Changing Format Of Data In Gridview Using VB

Mar 24, 2008

Hello all,
I am trying to modify the output of a SELECT statement in a VB asp.net page that pulls data from a SQL DB. I only need to modify one column in the gridview, but I'm having some issues.
Here is the situation:SELECT     TOP 24 ID, RecID, Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM         mTrafficORDER BY Timestamp DESC
The Timestamp column returns a number value (i.e. 564566).In terms of a date, 564566 doesn't mean anything to the user, so I need to convert this number into a recognizable and accurate date.
The formula I need to implement is Timestamp/1440 - 1
The following SELECT statement returns the number as a date, but makes all records the same date:
SELECT     TOP 24 ID, RecID, Convert(datetime, Timestamp/1440 + 1) as Timestamp, Answered, Holds, Dropped, Waits, Voicemail, Busied, LiveWaitsFROM         mTrafficORDER BY Timestamp DESC
What do I need to do to implement this function dynamically into my gridview?
Thanks in advance!
 
Chris

View 2 Replies View Related

DTS To Excel Export - Changing Format

Oct 26, 2004

Hi,

I have a dts package which is reading from a sql table and writing it to an excel file, its working fine except that I have a decimal field in the sql table but in excel file its writing it as string field.

The way I create this package is that I create a template file and I format that column to a "Number" format. Then I take this template file, rename it, export all the data to this file.

But when I open this file that decimal field is displayed as a string column and its left aligned.

Is there any way to fix this problem?

Thanks,

View 2 Replies View Related

Changing Identity Column Format

Mar 19, 2007

Hi,

Is it possible to change the format of the identity column? i.e. I have and integer value that will be the ID for the records to follow in the database. Instead of the values being 1,2,3,4,5,6,7,8,9 etc I want them still to be in sequence but I want the format to be 001,002,003,004,005,006,007,008,009 etc.

Is it possible to do that in SQL when you're creating the table?

View 1 Replies View Related

Changing Data Format On Select

Jul 20, 2005

Hey all,I have a basic table that looks something like this.CREATE TABLE MyTable(ID INT IDENTITY PRIMARY KEY,Company_ID INT NOT NULL,Round VARCHAR(50) NOT NULL,Details VARCHAR(250) NOT NULL)It has a few rows of data that look like this:Identity Company_ID Round Details--------------------------------------------1 5 A Blah, blah.2 5 B Generic data, blah blah.3 5 WERT More generic blah blah.Now what i'm trying to do during my select statement is select all the rowsthat belong to company_id 5 but if any of the rows round value contains thetext "WERT" convert that text into just a "--" for presentation purposes,but still select that row. I can't seem to figure out how i would transformthe text in the select statement? My immediate thought was substring /replace but i would need to combine it with an if else statement which i'veno idea how to make work in a select (sub-query maybe?) statement. Is thispossible? Perhaps i'm stuck iterating through the returned data within theclient application before presenting?Any help, as always, would be greatly appreciated.Muhd

View 5 Replies View Related

Changing Date Format And Casting As Varchar?

Jul 21, 2014

My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?

Current Code:

Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,

View 3 Replies View Related

Reporting Services :: Changing Date Format In SSRS

Nov 5, 2015

Fields!TaskStartDate.Value &  Fields!TaskName.Value & Fields!StatusForExecutiveReporting.Value & Fields!NotesForExecutiveReport.Value

This is my expression in a cell.when i run my report  task start date value is something like the:

01/25/2015 8:00:00AM.

I want this date to be in this form: jan 2015

View 3 Replies View Related

Integration Services :: How To Load Data Without Changing Excel File (CSV Format)

Jul 14, 2015

Facing problem while loading date in MS SQL Server 2005 from excel file (csv format).

How to load the excel file data without changing the excel file (csv format) .

see the [Start Date] and [ Exp End Date] having values like this : " 2015/07/31"

View 5 Replies View Related

SQL Server Date Format Query

Aug 16, 2007

Dear All,

I'm having a query problem regarding to the date format. From a table, there's a record of patients' birthdate.

In order to identify their age, how should i perform the query?

Select * from patient where BirthDate.Year < 1950

I've tried the above query where i want to extract patients' records who born before 1950, however, it generates error. Can somebody help?

M i K e

View 7 Replies View Related

SQL Server 2012 :: Converting Query Output To HTML Format

Mar 23, 2015

I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.

The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.

/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,

[Code] ....

The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.

<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>

If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.

Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.

[Code] .....

I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.

View 6 Replies View Related

SQL Server 2012 :: Making Query For Presenting Data In Different Format?

Jun 18, 2015

I have below table:

IF OBJECT_ID('tempdb..#complaints') IS NOt NULL
DROP TABLe #complaints

--===== Create the test table with

create table #cs([Year] float,
[Week] float,
[Month] float,
[C#] float,
[Dept] nvarchar(255)
,[Issue] nvarchar(255), [Type] nvarchar(255), [Dept age] nvarchar(255))

--===== All Inserts into the IDENTITY column

INSERT INTO #cs
([Year], [ Week], [ Month], [C#],[Dept],[Issue], [Type], [Dept age])
SELECT 2015, 14, 4, 188, D1,I1,T1, 5 UNION ALL
SELECT 2015, 14, 4, 452,d1, I1, T2, 5 UNION ALL
SELECT 2015, 14, 4, 63, d1, I1, T1, 6 UNION ALL
SELECT 2015, 14, 4, 9, d1,I2, T1, 7 UNION ALL
SELECT 2014, 14, 4, 187, D1,I1,T1, 5 UNION ALL
SELECT 2014, 14, 4, 451,d1, I1, T2, 5 UNION ALL
SELECT 2014, 14, 4, 62, d1, I1, T1, 6 UNION ALL
SELECT 2014, 14, 4, 10, d1,I2, T1, 7 UNION ALL)

and i want to have a table in below format: (query for it)

Week, Month, C1#,c2# Dept,Issue, Type, Dept age
14, 4 188 187 d1 i1 t1 5
14, 4 452 451 d1 i1 t2 5

I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.

View 9 Replies View Related

QUERY : Changing Row To Column

Apr 21, 2008

Hi all,

I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.

Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')

The expected result :
Line with check = 'N' must be converted to column(s).

Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)

Note : I would like to avoid using any cursors if possible.

Thanks in advance.

cheers,
erwine

... sql is fun...

View 3 Replies View Related

Changing The Query Based On Value

Jan 5, 2008

in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:

example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70

i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."

any idea on what i am doing wrong?

ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007
@SITEID INT,
@SENSOR VARCHAR(10),
@MIN INT,
@MAX INT,
@YEAR INT


AS
DECLARE @S AS VARCHAR(12)
SET @S=(
SELECT
CASE @SENSOR
WHEN 'TS1' THEN 'TS1MIN'
WHEN 'TS2' THEN 'TS2MIN'
WHEN 'TS3' THEN 'TS3MIN'
WHEN 'TS4' THEN 'TS4MIN'
WHEN 'TS5' THEN 'TS5MIN'
WHEN 'TS6' THEN 'TS6MIN'
WHEN 'TS7' THEN 'TS7MIN'
WHEN 'TS8' THEN 'TS8MIN'
END AS SENSORVALUE)




select top(10)*
into #tempMin
from sitecontroldataarchive
where siteid = @SITEID
and @S < @MIN --this is where the error occurs,
SELECT * FROM #tempMin

View 11 Replies View Related

Changing Database Has Slowed Down Query?

Jul 15, 2015

I had a query that executed in about 20 seconds pulling from database 1. The only changes I made were to make it pull from database 2. It is currently running and has been for over an hour and a half.

View 2 Replies View Related

Changing The MDX Query Based On A I/P Parameter. Is This Possible?

Jul 8, 2006

Hi



I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.

First of all i want to know if this is possible and if yes, how?

Also , Is it possible to open a window form on clicking any report data

(Similar to assigning hyperlink, but i want to open a window form instead!!!)





regard



Sai

View 2 Replies View Related

Changing Field Lenght Via Query Analyzer

Jul 17, 2004

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB

its 70 rights now I want change it to 120 its a nvarchar


how can I do it , I am using a ASP page


Thanks

View 1 Replies View Related

Changing Query To Point To DB Table But Has Errors

Aug 5, 2014

CREATE TABLE SampleData (
CoID nvarchar(255),
Company varchar(255),
FirstName varchar(255),
LastName varchar(255),
JobLevel varchar(255))

[Code] ....

I am trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.

What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.

My failed attempt:

Select *
From CampServ.dbo.SampleFakeCustomerData
//This is where I am getting stuck
WITH Priorities(Priority, JobLevel) AS(
SELECT 1, 'VP' UNION ALL
SELECT 2, 'Director' UNION ALL

[Code] ....

View 2 Replies View Related

Way To Format Query

Oct 22, 2013

I have to fetch some rows within a Crystal report. I need to select

from Orderhist a where
order# a = order# cr
and trncde is in 'pwb' 'pdb' 'pbb'

but i need then that trncde as a report field. also the order# cr is a formula field because its a subscript embedded 8 char within a 24 char. this is already available to the cr but can u do the where = on a formula field?

View 1 Replies View Related

How Can I Use Format As HH:MM:SS From Seconds Within The SQL Query

Nov 8, 2007

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.
cmd = "select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;
Please help me how to format this within the Query to display in GridView.

View 3 Replies View Related

Query Criteria Format

Feb 9, 2004

I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg

The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')

The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC

SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol

Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?

Thanks,

Paul

View 1 Replies View Related

Query Regarding Date Format

Jul 20, 2005

select last_name,hire_date,to_char(hire_date,'DAY') DAYfrom employeesorder by to_char(hire_date-1,'d')i wanted to know how the function to_char(hire_date-1,'d') works...its basically a query used to find the day on which a employee washired ,also it requires that the query be sorted by the day of theweek on which the employee was hired.it'll be helpful is someone replies to this

View 2 Replies View Related

Query Format Date

Sep 10, 2007


Hi
I wrote query to get date from database SQL 2000 server. Sometime I get different date format not MMDDYYYY but number as Julian calendar?? Do you know what wrong with this? How can I wrote SQL so that all date will be MMDDYYYY?
Thanks
Daniel

View 1 Replies View Related

View The SQL Query Out-put In Horizontal Format.

Jan 10, 2005

I have the following SQL query that I like to view the out put in horizontal format:

Select ID, First_name, Last_name from ABC

Instead of getting out-put like

Id First_name Last_name
1Jim Smith
2Tom Jones

I like to see the out-put like:

Id1 2
First_nameJimSmith
Last_nameTomJones

Please advice.

Thanks in advance,


Abrahim

View 1 Replies View Related

Convert Smalldatetime To Mmm Yy Format In Query

Jan 7, 2004

I am trying to write a simple query that retrieves the data field from a table (stored in the smalldatetime format) and converts the date to mmm yy format. The closest I can get is retrieving the date in the dd mmm yy format using the query below.

select convert(varchar(10),DATA_DATE,06) As DATA_DATE

If there an easy way to parse out the information I want? I also attempted to use the SUBSTR functions, but they always returned error messages.

View 3 Replies View Related

SQL Vs Access Date Query Format

Mar 7, 2004

I am new to SQL server, and am learning as I move an Access db to MSDE2000A. With Access db I run several different queries from a VB6 application in the basic format:

SELECT testdb.*
FROM testdb
WHERE testdb.datefield = #1/01/2004#

When working with the MDSE version of the db, problem is with the "#" delimiter of dates. MSDE is giving a bad query error. If I change the format to: datefield = '1/01/2004' , the query works on MSDE

However, using SQL builder in VB Design Environment I can run the query in either format and get a result.

Any suggests what I am missing? Thanks.

View 2 Replies View Related

Sql Query To Get A Desired Format Of Display

May 23, 2008

Hi Guys,

Below is example of the current structure of table1 I have at run time:

------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically

Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont

Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------

I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.

The table formaating is lost in HTML view but just consider any gaps between fields as next column value.

Am I clear in what i am asking , if not please ask me.

Any help will be highly appreciated.

View 2 Replies View Related

Data Format Consistency Or Query?

Oct 2, 2006

Hi

I have a table that stores the period and year as two seperate fields the problem that i have is that when the data is entered in the data base the period is sometime entered as 6 or 06, hence i have a table thaat looks like as follows:


Period Year
====== ====
6 2006
06 2005
12 2006
3 2005
2 2005
4 2005
04 2005
03 2005



when i currently query the information using the period i use the LIKE command in SQL which gives me the results but the problem is when the like is done with 2 as the period value it also retrieves the 12, 02, and 2, in that case the period 12 was not that was requested.

I was wondering is there a way to reformat the data so that all the data is in a consistent format?

Or is there a better way of quering the information in the current format?

View 13 Replies View Related







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