Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query

Sep 11, 2015

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the following

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].

View 10 Replies

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Question - Replicating Schema Without Data For Specific Columns

Aug 30, 2007

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

View 13 Replies View Related

Data Access :: Retrieve Schema Information Of Columns Of Tables

Sep 10, 2015

Till recently we were using the following code to retreive schema information of columns of tables

Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})

Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :

SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]

Then how can I retrieve the schema information of only the columns present in the query.

(Its possible that i might get a query with multiple tables with joints)...

View 3 Replies View Related

How To Specify Datatypes For Excel Columns?

Oct 11, 2006

So I have Excel Source in my dataflow - so I do a simple transform and try to save it to db and guess what - a freaking error message: Columns blahblah and BlahBlah cannot convert between unicode and non-unicode datatype. I cant figure this MS stuff out - why the heck is this data from excel unicode to begin with & I just dont feel it is right to always use the derived columns to convert datatypes when dealing with Excel data. Am I missing something again or is it another one of those MS gotchas?

--
Note - all profanity in this post is replaced with words like freaking, stuff, gotcha and so on by the author

View 10 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Transact SQL :: Update Query Of Two Columns

Oct 23, 2015

I want to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1. It is some tricky because the value is NULL in some records of NewVol1 and NewVol2.

Update query. SAP, Code, Code1, Code2 and SAPdate are keyfield when join both table to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1.

Create table #Rec1 (SAP char(10), Code char(6), Code1 char(6), Code2 char(6), SAPDate datetime, NewVol1 char(20), NewVol2 char(20))
insert into #Rec1 values ('SATRIP','AAA','AA','A','2014-01-01','838.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','BB','B','2014-01-01','839.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','CC','C','2014-01-01','848.88484884',NULL)
insert into #Rec1 values ('SATRIP','BBB','AA','A','2014-01-01',NULL,'383.48377373')
insert into #Rec1 values ('SATRIP','BBB','BB','B','2014-01-01',NULL,'385.48377333')
insert into #Rec1 values ('SATRIP','BBB','CC','C','2014-01-01',NULL,'675.48377234')

[code]....

View 4 Replies View Related

Query Not Returning Proper Data (date Related) In 2005 After Upgrade From 2000...

Jun 7, 2007

I am sending out an SOS.

Here is the situation:

We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can��t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn��t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea��s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.

Thank you. Scott

View 4 Replies View Related

Transact SQL :: Query To Make Rows To Columns

May 18, 2015

I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)

name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english

Desired output

name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english

View 4 Replies View Related

Transact SQL :: How To Return Content Of Table As Columns In Query

Oct 1, 2015

I have

Customer table, tblCust: ID; Name

1 Peter2 Mary
Product table, tblProduct: ID; Name

1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount

1 ;2 ;$20 – means Peter ordered $20 oranges

How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?

Name Banana Orange Apple

Peter 0 20 0

View 4 Replies View Related

Transact SQL - Combining Two Columns Of Data

Oct 23, 2001

How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).

Thank you,

Andrew

View 2 Replies View Related

Transact SQL :: Striping Data Into Two Columns

Jun 3, 2015

In SQL statement is it possible to split the below column into two columns?

COLUMN_AAAAA AAAAAAA CO A.A. # 4030
BBBBBB BBB BBB CO. # 4260
CCC CCCCC #3001

View 16 Replies View Related

Transact SQL :: Put One Column Data Into Many Columns?

Oct 15, 2015

how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.

Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A B C D E F G
xxx yyy zzz aaa bbb ccc ddd

View 41 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Renaming Columns Based On Data Using Transact Sql

Jan 18, 2008

A happy belated New Year to all!

Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?

example

Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'

I want to use the month and year of the date to rename the column.

Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'

Thank you in advance for any help you can offer.

TMendez

View 5 Replies View Related

Transact SQL :: Generate Unique Username From Data Columns

Oct 19, 2015

I am trying to achieve the below problem statement, however I am getting stuck at the looping part.

EX: We have three columns

First Name | Last Name | Mothers Name

I want to generate the username field using above columns, so lets consider, I have following data

First Name | Last Name | Mothers Name
a                    b                  cdef
a                    b                  cdfg
a                    b                  cdfj

Expected Usernames:

1: a.b
2: a.b.c
3. a.b.cd

Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)

View 4 Replies View Related

Transact SQL :: Split Data Into Two Columns Based On Where Space Appears

Jul 8, 2015

I have a field in a table that contains addresses e.g

15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road

I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.

So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.

View 6 Replies View Related

Selecting Exactly 1 Row But More Columns From A Related Table..

Jan 4, 2008

I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...

View 6 Replies View Related

Transact SQL :: Query To Avoid IF And CASE And Calculations Carried Out Once Only To Speed Up With Common Comparing Columns

Oct 22, 2015

Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:

PurchaseDate
TownName
QuantityPurchased
Tank1_Size
Tank2_Size
Tank3_Size
Tank4_Size

Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled

then again IIF but this time to check:

Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled

View 2 Replies View Related

Transact SQL :: How To Get Time Span Across Several Related Records

Nov 19, 2015

I need a boost to understand how to get started on solving this SQL Query.

TicketId Seq StatusDateTime

T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000

I need to find the total StatusDateTime for each TicketId
I need to find the average StatusDateTime for all TicketIDs
Ex. TicketId, "T10001", has 4 records based on the Seq column.

By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.

Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.

View 6 Replies View Related

When Previewing, Columns Aren't Rendered? SP2-related?

Nov 12, 2007

Okay, when i go and preview a report that has columns (my report is designed for printing lables, i'm just pulling names and addresses and placing them in a list text box and 3 columns), the columns do not show. Keep in mind this is in "preview" cause html cannot handle columns.

On the first page, the middle column is blank, then the second page has blank first and last columns, and this repeats throughout the preview. So column 2 is blank, then 1 & 3 are blank, and so on.

I'm wondering if this is a SP2-related thing cause i know SP2 caused the black-preview-renderings (and i fixed that).

I know it's working properly cause i can export to PDF and it's perfect.

So, this is merely a rendering issue (at least so far). Does anyone have any suggestions or any advice to a fix on this? I CAN'T BE THE ONLY ONE? It'd be very nice to be able to preview my reports before they're deployed to the intranet website.

Thanks in advance!

View 11 Replies View Related

Transact SQL :: Extracting From 3 Related Tables And Including MAX Function

Oct 5, 2015

I have these 3 tables in a database for printed chamber music works:

Table INSTRUMENTS: List of musical instruments
Column ID -- int, primary key
Column sInstrument -- string

Table PTRS_INSTRUMENTATION:
Column ID -- int, primary key
Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
Column lMainItem -- int, points to a row in table MAINITEMS via ID
Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item

Table MAINITEMS: List of chamber music works
Column ID -- int, primary key
Column sItem -- string

Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).

I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),

And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).

View 7 Replies View Related

SQL 2012 :: Using Money Column To Store Amount Related Columns?

Sep 3, 2015

I just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?

View 3 Replies View Related

Moving Data From One DB Schema To Another DB Schema Using SSIS

May 8, 2007

Hello,

I would like to use SSIS tool to move the data from one database schema to another database schema.

For example:

Source table has

1. UserName (varchar 20) (no null)

2. Email (varchar 50) (can be null)

Destination table has

1. UserID (uniqueidentifier - GUID)

2. UserName (varchar 50) (no null)

3. EmailAddress (nvarchar 50) (can be null)

4. DateTime

Questions:

1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?

OLE DB Source, OLE DB Destination, Data Converson and .....

How do I insert Guid and Date at the same time?

2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.

Please give me some advices here.

Thanks.

View 3 Replies View Related

SQL Search :: Query For Finding Two Columns Data In Third Column And Getting Data From It?

Jul 6, 2015

getting result as given below.

Input:

id Name Data

101 AA PQ102BBRAJAKIRANBUBLU

102 BB RS101AAEROJUCHALABAGUNDI

103 CC TU104GGANDICANKILLANYONE

OUTPUT:

id Name Data

101 AA 101AAEROJUCHALABAGUNDI

102 BB PQ102BBRAJAKIRANBUBLU

103 CC

View 4 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

Mapping SQL Server Data Types To C DataTypes

Jul 20, 2005

Hi Can anyone point me to a document somewhere that shows a mapping ofSQL Server 2000 datatypes to C datatypes? I am writing some extendedstored procedures which need to be able to process pretty much anydata type, so I want to make sure I am taking them from SRVPROC andstoring them in the correct C data type.Thanks,Bruce

View 1 Replies View Related

Changing DataTypes In An Excel Data Source

Jun 7, 2006

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

View 11 Replies View Related

DTS Of Data Between Two Tables, Same Field Names But Differnt Datatypes

Jul 23, 2005

I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?

View 3 Replies View Related

Query: Two Columns With Same Data

May 13, 2008

Hi,
I have 3 tables:
Table SLA: SLA_code, SLA_name
Table industry: Industry_code, Industry_name
Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq

I would like to query to get in one table:
SLA_name for DESTINATION, Industry_name, Freq

Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.

How can I query to get the Freq and Industry for those destination SLAs?

I have make a relationship between:
Table SLA.SLA_code with Table14.SLA_code_destination and
Table_industry.Industry_code with Table14.Industry_code

But I am not sure if just a
SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq
FROM SLA, Industry, Table14
would select the SLA_destination and not the SLA_origin.

Thanks very much in advance,

Maria

View 1 Replies View Related

Transact SQL :: DDL Trigger On Table Schema Change

Oct 16, 2015

Is to Possible to Create a Triggers to capture Schema (alter table, Drop table) Changes only for certain tables.I don’t want schema change for entire database.

View 7 Replies View Related

Transact SQL :: Expected XML Schema Document Error

May 1, 2015

Why do I get an error when creating XML schema as follows?

CREATE XML SCHEMA COLLECTION PersonalInfoSchema AS
'<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/xmlSchema">
<xsd:element name="personal">
<xsd:complexType>
<xsd:sequence>

[Code] ....

View 2 Replies View Related

Transact SQL :: Query For Yesterday Data

Dec 2, 2008

Howto write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i don't want. everyday i need to search the data from yesterday whole day.

View 13 Replies View Related