Transact SQL :: Creating Dummy Headers (column Names) With Union All

May 19, 2015

I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....

COL 1     COL2      COL3       COL4 etc.                         BEH                             BIG       BPL etc.

*************************************   INTAKT       DEFEKT       INTAKT DEFEKT          INTAKT

*************************************       B                E                 B         E                    B

I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?

View 4 Replies


ADVERTISEMENT

Transact SQL :: Get Table And Column Names From XML

Jul 6, 2015

I am trying to find the Table and column names from the below.

Is there a way i can get table name and column name from query_plan column?

SELECT TOP 100  text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'

View 5 Replies View Related

Transact SQL :: Getting Table And Column Names

Jul 21, 2015

 SELECT TOP 100  text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
  JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'

I have below output:

Text      Query_plan     Plan_handle     Lst_execution_time
Select id,name from person                  
<Showplan...   dshkkgdaHqrqe13232423    2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc'      
<Showplan...    poasfvrqe13232423    2015-07-21 10:16:22.500
delete from orders where  ORid=8    
<Showplan...     2ase2423     2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num  
<showplan            afqfqfq   2015-07-21 10:10:22.710
 
I am looking for 

Text           Query_plan                   Plan_handle      Last_execution_time        TabName colname
Select id,name from person  <Showplan... dshkkgdaHqrqe13232423   2015-07-21 10:50:22.713  
Person Id, name Update customer set Cid=3 where name='abc'  
<Showplan   poasfvrqe13232423   2015-07-21 10:16:22.500  customer  Cid,  name
delete from orders where  ORid=8   <Showplan...   2ase2423     2015-07-21 06:10:22.710    Orders    ORid
Select 1,2,3,4,5 from num <showplan            afqfqfq   2015-07-21 10:10:22.710        nUM      1,2,3,4,5

View 2 Replies View Related

Transact SQL :: Bulk Column Names Rename

Aug 4, 2015

Is there a way to bulk remove spaces from column names from all tables in a db? 

View 6 Replies View Related

Transact SQL :: Vertical Column Names With Data?

Jul 14, 2015

I have a table as below and need getting the desired result as below

Col1 Col2 Col3
A B
C

---desired result

t1 t2
Col1 A
Col2 B
Col3 C

[URL]

View 3 Replies View Related

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where Value = 1

Aug 30, 2007

Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.

The function of this, I believe was to make the select work but not retrieve any actual values.


I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.

What I am thinking may not even apply to what I need to do here.

If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.

Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table
but I was hoping this would work.

Example:

Insert Into table
Select
1 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'CA'

Union
Select
2 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'NJ'

Union

/*Final Select */

Select
6 as RecId,
' ' as field1,
field2
From test1
Where 'some value' = 1'

Thanks much for your assistance!!!

TADEG

View 1 Replies View Related

Transact SQL :: Find Table And Column Names From String Data

May 29, 2015

I have a SQL text column from SP_who2 in table #SqlStatement:

like 1row shown  below :

 "update Panel  set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"

I want to find what table and column  names are in the text ..

I tried like below ..  

Select B.Statement from #sp_who2 A  
LEFT JOIN #SqlStatement B ON A.spid = B.spid 
 where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C 
ON T.object_id=C.object_id
WHERE T.type='U'


Something like this : find the column names and tables name

View 18 Replies View Related

Transact SQL :: Renaming Pivot Table Column Names Dynamically

Oct 30, 2015

I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.

I have a requirement to rename the column header where the previous month end balance period be renamed to P0.

if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.

How do I dynamically rename the column headers.

View 7 Replies View Related

Transact SQL :: ALTER Vs UPDATE When Creating A New Column With Default Value

May 8, 2015

I have a table with ~30M records. I'm trying to add a column to the existing table with default value and have noticed following ... When using alter with default value- (Executes more than 45 min and killed forcefully)

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT CONSTRAINT DF_Table_X_is_Active DEFAULT 'FALSE' NOT NULL
GO

When using update command after adding column with alter (without default value) it completes is 5 min.

ex:  
ALTER TABLE dbo.Table_X Add is_Active BIT NULL
GO
UPDATE Table_X SET is_Active = 0 WHERE is_Active IS NULL
GO

Why there is so much of difference in execution times ? I was just trying to understand internal behavior of the SQL in these two scenarios. 

View 4 Replies View Related

Reporting Services :: Add A Variance Column Into SSRS Report Where Column Headers Are Non-static?

May 13, 2015

I am creating a report in SSRS which has the following criteria:

- Row 1 (parent) is 'Product'

- Row 2 (child) is  'Feed'

- Columns are date.  I have 5 dates showing at any one time across the top.  The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.   

 - Data is the number of records.

I have a sub total on the Product and the report is collapsed on Product as default.

What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates.  So the difference between the date the user selected (@date parameter) and the day before that. 

View 6 Replies View Related

Transact SQL :: Add Headers Name In CSV File From Stored Procedure

May 11, 2015

I want to add some name of the column's header from stored procedure to the CSV file

bcp "EXEC [databasname].[storedprocedure]" queryout C:estbcp_outputTable.csv -c -T -SPC01 -r


I'm using the code above in CMD.

Unfortunately, I cannot change the code that is inside of the stored procedure because it is already used in productionphase.

View 5 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

Getting Column Headers

Apr 12, 2006

Is there any way to retrieve the column header name along with your query data?

View 2 Replies View Related

Supressing Column Headers

Oct 27, 2005

hello,

Just curious if we can supress column headers in SQL Server..

example:

create table Empmaster
(
empid int identity(1,1),
empname varchar(10),
empsalary numeric
)

insert Empmaster(empname,empsalary)values('Imran',5000)
insert Empmaster(empname,empsalary)values('Raja',5000)
insert Empmaster(empname,empsalary)values('Ram',8000)

Sql:
Select empname, empsalary from Empmaster

would have a result set like (this is not an exact result from analyser)

empname empsalary
----------- ------------
Imran 5000
Raja 5000
Ram 8000

But I was wondering if I could somehow make it display

Imran 5000
Raja 5000
Ram 8000

I actually implementation of this is something vast, but I have illustrated a very simple example to explain what I want to achieve. Might seem a very simple 1 line solution, but I haven't been able to find it. .If ther is any documentation that might help please reply and point me to that..

thank you
:)

View 3 Replies View Related

Column Headers For Matrix

Sep 26, 2007



I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?

View 3 Replies View Related

Column Headers At The Top Of The Page....?

Jul 23, 2007

I have a report looking something like this:



Field 1 Field 3 Field 5

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2



Field 2 Field 4 Field 6

RandomInfo1 RandomInfo1 RandomInfo1

RandomInfo2 RandomInfo2 RandomInfo2





It extends along a fair number of pages, and I'm currently using the "# of columns = 3" in the Report properties --> Layout section in order to make it span over 3 columns instead of the usual 1. I'm using a table in which the random info fields are the repeating values, and the Field 1,2,3,4,etc are included in the list control, but not part of the physical table itself.



My question stems from a couple of chunks of data which basically extend over the course of 1.5 pages (yep. There's enough to fill about 4.5 columns worth.)



I currently have



Field 1 RandomInfo50 RandomInfo100

RandomInfo1 RandomInfo51 RandomInfo101

Randominfo2 RandomInfo52 RandomInfo102

etc.... etc.... etc....



-----NewPage----



RandomInfo150 Field 2

Randominfo151 NewRandomInfo1

RandomInfo152 NewRandomInfo2

etc...



What I'm looking to do is have "Field 1" appear at the top of each column whenever a larger chunk of data forces itself into multiple columns, or even multiple columns on multiple pages. I have this nasty feeling it's something silly and blatantly obvious I'm missing...



Any thoughts?

View 3 Replies View Related

Creating A View Using A Union?

Jan 14, 2015

I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.

This is my code so far:

Create View Table_AB As
Select *From Table_A
Union All
Select*From Table_B

View 1 Replies View Related

Column Headers And Rows From 2 Tables

Dec 16, 2014

I need to write a sql statement to retrieve column headers and rows from 2 different tables.

E.g.:

row1: EMP column list, tag: emp
row2: DEPT column list, tag: Dept
row3: EMP columns data
row4: DEPT columns data

And the data sorted by deptno column

E.g.:

EMP, Deptno, empno, ename, age, ...,
DEPT, Deptno, ename, loc
EMP, 10, 7548, Justin, 43,....
DEPT, 10, marketing, california
EMP, 20, 7345, Carol,26,....
DEPT, 20, finance, New York
EMP, ....
DEPT,...
.....
......

View 3 Replies View Related

Error When Creating View With Union

Jul 10, 2014

I’m receiving the following message when attempting to run the SQL statement below.

Error report:
SQL Command: force view "UIP_SOC"."SEG_VIEW_EWO_2"
Failed: Warning: execution completed with warning

-----------------------
CREATE OR REPLACE FORCE VIEW "UIP_SOC"."SEG_VIEW_EWO_2" ("CODE", "NAME", "EWO4", "EWO6") AS
SELECT DISTINCT code, name
FROM
(
SELECT seg_value AS code, seg_desc AS name, SUBSTR(seg_value,5,4) AS EWO4, SUBSTR(seg_value,5,6) AS EWO6
FROM UIP_SEGMENT_VALUES
WHERE seg_name = 'EWO' AND seg_value IN (SELECT ewo FROM stage_budget_v)
UNION
SELECT CODE,NAME FROM SEG_VIEW_PARENTS WHERE SEG_NAME = 'EWO' AND NOT (CODE IS NULL)
);
----------------

Referenced View Columns:

"SEG_VIEW_PARENTS" ("SEG_NAME", "CODE", "NAME")

Referenced Table Columns:"UIP_SEGMENT_VALUES"
"SEG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SEG_DESC" VARCHAR2(200 BYTE),
"SEG_TYPE" VARCHAR2(20 BYTE),
"SEG_COMPANY" VARCHAR2(20 BYTE)

View 1 Replies View Related

Matrix Column Headers Not Displaying In IE7 (but Ok In PDF &&amp; Preview Tab)

Apr 29, 2008

My matrix column labels do not appear at all at the lowest level column grouping when viewing in IE7

All is ok when viewing the pdf, or when viewing using the preview tab.

Is this an ie7 bug?

View 2 Replies View Related

How Can A Matrix's Row And Column Headers Be Made Floatable?

Dec 25, 2007

when paging down or across in a matrix based report, it would be nice for headings (row and column) to float (ie not disappear) with the position of the report. Is this possible in RS?

View 10 Replies View Related

Need An Efficient Way To Repeat The Column Headers Of A Table

Dec 19, 2007



There are 2 tables
tPeople

ID Name

1 Arun
2 B
3 C

tAddress

PeopleID Address
1 Test Address A
1 Test Address B
1 Test Address C
2 Address A
3 Address1
3 Address2

Expected Result

ID Name Address1 Address2 Address3

1 Arun TestAdressA Test Address B Test Address C


2 B Address A

3 C Address1 Address2

Address columns repeated = max(count(Address)) for PeopleID

Help me on this!

I can do it through dynamic sql using Execute sp_executesql

Is there any workaround except this?

View 2 Replies View Related

Interactive Sort On Column Headers In Matrix

Dec 7, 2007

Hi there,

I'm trying to implement Interactive Soring on the column headers in a matrix in a report. Is it actually possible to do this? I've read several internet posts and stories of implementing the Interactive Sorting in the upper-left corner of the matrix, but this is not what I want, I want to implement it on the column headers .

The goal I'm trying to achieve is to give the user the possibillity to click on a column header to sort the rows below in asc- or descending direction.

Please tell me if this is possible, because all my efforts have not been succesfull! If it's possible, please provide the solution to do this.

If you need more information, please don't hesitate to ask.

Thanks in advance,

Dave Ruijter
BI Consultant

View 3 Replies View Related

Alternative To Creating View With Union In Two Databases?

Jul 20, 2005

I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt

View 2 Replies View Related

SQL 2012 :: SSRS Report With Dynamic Column Headers

Sep 22, 2013

I have a report which runs for last 12 months data. Since this is going to be last 12 months the column headers change every month. How can we implement this with dynamic column headers in the dataset?

View 9 Replies View Related

Exporting Query Analyzer Data With Column Headers...?

Jul 20, 2005

Hi,Does anyone out there know how to do this? I've been banging head forawhile now trying to answer this seemingly simple question.tia,Mike

View 3 Replies View Related

Report Design Question Two Horizontal Column Headers One Below Other

Jan 30, 2007

I have a report requirement, i am new to reports.

I want to have two lines of column headers.( with 7 columns in parent columns header)

and 7 columns in child column header.

The first column header will show a parent record.

And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.

its a one to many: 1 parent record and below all child records for that parent record.

can i use subreports controls to show all child records of the parent.

Thank you very much for the information.

 

 

View 1 Replies View Related

How To Show A Line Between Table Column Headers And The Details

Jan 8, 2007

I am using table object to present report.

table row1 has all the column names and table row2 has the data.

How can i put a horizontal line between column names and the data rows.

Thank you very much.

View 1 Replies View Related

Mapping Column Headers From Source To Rows In A Spreadsheet

Oct 17, 2006



Hello,

I am trying to do the following:

I have been given an MS Access Database that has a table with columns

I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).

Any suggestions???

View 1 Replies View Related

Deadlock In View With Select Union - Creating A/S Dimension

Jul 23, 2005

I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null

View 1 Replies View Related

SQL Server 2014 :: Display Column Headers And Rows From Different Tables?

Dec 18, 2014

I am looking for SQL query which uses 2 tables CASH and BALANCE.

eg: Need Tablename, ColumsList and data in the results set.

eg: 10 rows shown below and ordered based on Acct_number

Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE

Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY

Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row6,BALANCE,10,500,750,DR,12/12/2014,USD

Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05

Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07

Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05

Row10,BALANCE,20,500,750,DR,12/12/2014,USD

View 7 Replies View Related

Table Column Headers Are Not Repeated On Each Page When A Row Spans Pages

Jul 5, 2007

If a single row of data spans a page then the column header is not repeated until the next row on the next page.

Is there a way to overcome this?

View 1 Replies View Related

Excel Connection Manager Where There Are Multiple Rows Of Column Headers

Oct 11, 2007

I have excel files where the column headers I care about are on line 5, and the actual data doesn't begin until line 6. Other than deleting the first 4 lines, which is impractical, how can I get the Excel Connection Manager to import the data correctly? I was able to do this under DTS, so I have to imagine it's possible.

Thanks!

View 1 Replies View Related







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