SQL Server 2008 :: Display Output Of Table In Given Format?

Aug 19, 2015

I have a table Test123 having three column EmpID,AttribName,AttribValue.

run the below query to generate table structure and data.

Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))

insert into Test123 values(1,'Name','X')
insert into Test123 values(1,'Age',50)
insert into Test123 values(1,'Salary',1000)
insert into Test123 values(2,'Name','Y')
insert into Test123 values(2,'Age',30)
insert into Test123 values(2,'Salary',2000)
insert into Test123 values(3,'Name','Z')
insert into Test123 values(3,'Age',35)
insert into Test123 values(3,'Salary','One Hundred')

And I want output in below format.

AttributeValueType
=================
AGE | NUMERIC
NAME | ALPHABET
SALARY | ALPHANUMERIC
==================

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Display Stored Procedure Output In Html Table Format

Mar 16, 2014

i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.

View 2 Replies View Related

SQL Server 2012 :: Output Of Table Required In Specific Format

Apr 17, 2014

I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.

I tried with pivot but does not seems to be working.

View 2 Replies View Related

SQL Server 2008 :: Convert Table Into XML Format

Oct 5, 2015

I want to convert table into following xml format. Sample two records is given below.

<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related

How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?

Mar 11, 2008

This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1


Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called €œHexMap€? that contains some columns that I am ready to insert some row data into. Here is what my program should do:

As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:

http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon

I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.




Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter

'Consider using Me._adapter that is used already

CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()

'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then


MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If

Try

Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try

'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================

Dim rows As Integer

rows = 0

Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)

CN.Open()

rows = CMD.ExecuteNonQuery

If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If


If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================



Thanks for reviewing my code.

Technozoide

View 1 Replies View Related

SQL Server 2008 :: Display One Row For The Query?

Jan 29, 2015

For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on the logic that if I have multiple rows for one BL_ID, then only the one which has the largest LEG_SEQ_NBR should be displayed and the other rows should be ignored.

I am using the below code get to the above logic:

ROW_NUMBER() OVER (PARTITION BY ITIN.BL_ID ORDER BY ITIN.LEG_SEQ_NBR desc) AS RowNum

select
*
FROM
(
SELECT
TMIS.[BL_ID]
,[POL_NAME]
,[POD_NAME]

[code]....

where rownum in (1,2)

View 6 Replies View Related

SQL Server 2008 :: Display Dates Between A Range

Mar 30, 2015

How to show the dates in between a range. Its hard for me to explain so I have ddl with the original results and then ddl of how I would like the desired outcome.

On the side I have a visit ID I need to show each day logged for each ID. Sometime the Start and End are a single day and sometimes they are a range. I need a row for each date.

CREATE TABLE #Results (VisitID INT, DateFrom DATE, DateTo DATE)
INSERT INTO #Results VALUES (361, '2015-03-07', '2015-03-07'), (361, '2015-03-08', '2015-03-10')
,(48, '2015-03-18', '2015-03-18'),(48, '2015-03-19', '2015-03-23')

SELECT *
FROM #Results
DROP TABLE #Results

[Code] .....

View 5 Replies View Related

Reporting Services :: Exporting SSRS Output To Word Format And PDF Format Differs

Aug 19, 2015

I have created SSRS report which has many overlapping objects, the output in PDF format seems to good but in word format it is not giving the required output.

View 5 Replies View Related

SQL Server 2008 :: Display All Months Even If Values Are NULL

May 13, 2015

I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.

For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.

My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:

SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))

[code]...

View 8 Replies View Related

SQL Server 2008 :: Can Display All Days In A Range Even With No Data?

Aug 10, 2015

I need to display all the dates within a range even with no data

For example right now my query get the records with the range say...

The range is 7/1/15 thru 7/7/15

I Get...
Joe 7/1/15 xxx
Joe 7/3/15 ccc
Joe 7/5/15 xxx

I want...
Joe 7/1/15 xxx
Joe 7/2/15
Joe 7/3/15 ccc
Joe 7/4/15
Joe 7/5/15 xxx
Joe 7/6/15
Joe 7/7/15

View 9 Replies View Related

SQL Server 2012 :: Format Output As Date

Feb 28, 2014

I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.

, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'

View 3 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.

ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: Data Output In XML

Feb 22, 2015

I have below two table and i want ot generate the xml out put as mentioned below.

create table Candidates(eno int,ResumeText varchar(30), Email varchar(30),Active varchar(30),postalcode varchar(30),country int)
insert into Candidates
select 1,'Test','ee@ee.com','Active yes','888888',2
union all
select 2,'Test','ee@ee.com','Active yes','888888',6

[code]...

View 2 Replies View Related

SQL Server 2008 :: How To Remove Spaces From Output

Jun 23, 2015

When I save my output (from a query I ran) to a text file, there seems to be rows of spaces. Is there a way i can just kill off any spaces at the end of my query? Like rtrim or something?

View 9 Replies View Related

SQL Server 2008 :: Batch Archiving With OUTPUT

Oct 13, 2015

I have to set up archiving of some pretty large tables (125M+ rows) and I'm trying to figure out the best way to do it.

I've been using OUTPUT into, something like this:

delete top (100000) a
output deleted.RecordID
into dbo.RecordsArchive (RecordID)
from dbo.Records

It's difficult to tell if it sub-optimal code or just sheer volume, but any performance penalty using OUTPUT into in this way? or is it advantageous to insert the records to the archive first, and then delete them in two separate operations?

View 0 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 2008 :: How To Get Output Based On Sequential Number

Feb 13, 2015

The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.

Ex1:

CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051

Ex2:

CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052

How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.

View 1 Replies View Related

SQL Server 2008 :: SSMS Truncating PowerShell Output?

Jun 3, 2015

When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).

EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.

Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?

View 6 Replies View Related

SQL Server 2008 :: Get Date Format From String?

May 28, 2015

How can we identify the Date Format from a String in SQL Server.

I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.

Ex :

1. "02-20-2013" -> "05-28-2015"
2. "2014-04-19" -> "2015-05-28"

Any method to identify the DateFormat then it would have made the job very easy.

View 2 Replies View Related

SQL Server 2008 :: Date Format Not Converting To Dd / Mm / Yy

Sep 1, 2015

I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;

[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]

The results are ;

Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000

as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?

View 9 Replies View Related

SQL Server 2008 :: Unable To Open Step Output File

Aug 27, 2013

I am running sql script inside job.But sql is not saving the results in the file which i specified('Unable to open Step output file'). I tried same thing on different server, it works fine.Both are are 2008 R2.Is there any server level setting that i have to look at.On both servers i logged in as sal. Why is it working on server1 ,but not on server2?

View 6 Replies View Related

SQL Server 2008 :: Query To Show XML Output For Hierarchical Data?

Mar 10, 2015

selecting table data in hierarchical XML .

Here is the sample table DDL and data

Declare @continents Table
(
id int identity (1,1)
,continent_id int
,continent_Name varchar(100)
,continent_surface_area varchar(100)
,country_id int

[code]....

View 8 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

SQL Server 2008 :: Query Plan Output List (IsBaseRow)

May 6, 2015

Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.

View 3 Replies View Related

SQL Server 2008 :: Query Results Output Into CSV File To Use In VBA Macro

Jul 31, 2015

I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.

-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)

[Code] ...

Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):

557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)
^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000
^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Bulk Insert With A Format File?

Mar 5, 2015

I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.

View 3 Replies View Related

SQL Server 2008 :: Month Name And Year (2 Digits) Format

May 12, 2015

How to convert a date to the format as Month name and year(2 digits).

For e.g.- Jan 14, Feb 14......Mar 15

View 7 Replies View Related

SQL Server 2008 :: Show Different Last 6 Month In Format DD-MM-YYYY?

May 15, 2015

How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?

01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014

I have this Select statement

select replace(convert(char(11),getdate(),113),' ','-')

But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement

View 4 Replies View Related

SQL Server 2008 :: Function To Check Name Format (Last / First / Middle)

May 27, 2015

I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?

Legal Name ////////////////////////////////////// Legal_Entity_Struct

S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company

[Code] .....

View 0 Replies View Related

SQL Server 2008 :: Date Format Got Changed Between Two Different Version Of Server For Same Web Request

Sep 18, 2015

we are having the same code base in both the environment but we have different version of SQL Server Version(Env 1 - SQL 2012, Env 2 - SQL 2008). We are getting the expected result in Env - 1 but not in the ENV -2. So we tried to get the SQL trace for the web request we found that Date format getting changed.

2012 - Date Format in the SQL Trace for the request '2015-09-10 00:00:00' which is working as expected.
2008 - Date Format in the SQL Trace for the request 'Sep 10 2015 12:00:00:000AM' which is Not working.

Complete SQL Trace for Request.
Evn - 1 - SQL Server 2012
exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='2015-09-10 00:00:00',@EndDate='2015-09-18 00:00:00',@OrderDirection='D',@StartIndex=1,@PageSize=20

[code]...

View 3 Replies View Related

SQL Server 2008 :: Select Statements To Output Files With Proper Datestamp

Jun 11, 2015

I have few complex queries and I want to extract the output of results to all different dateformatted output files.

How to write the queries?

I know BCP is a solution but any other effective way to implement it?

View 2 Replies View Related

SQL Server 2008 :: Export Output Into CSV File As A Report With Column Headers?

Aug 20, 2015

I have a table which has few columns as Numeric value. I need to export the output into a csv file as a report with column headers. I have used bcp command. Here column name and Column Header name is the same. The BCP query which i have used is Below

bcp "SelectCompanyCode,MonthId,ActualityCode,CompanyCounterpartCode,LocalProductCode,LocalCustomerBillTo,
DestinationCountryCode,LocalCostCenterCode,LocalGLAccountCode,LocalProjectCode,TXCurrencyCode,
TXAmountYTD,LocalCurrencyCode,LocalAmountYTD,SourceSystemFromTable(nolock)" queryout D: est.csv -c -t ";" -r -S -T

Is there any way to export bcp with header or any other command to generate the csv file.

View 1 Replies View Related







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