Rotate Single Row Into Two (key, Value) Columns

Jan 30, 2007

 


Hi,


How can I convert this results :







col 1

col 2

col 3

col 4

col 5

col 6

col 7




val 1

val 2

val 3

val 4

val 5

val 6

val 7




into this






Col 1

val 1




Col 2

val 2




Col 3

val 3




Col 4

val 4




Col 5

val 5




Col 6

val 6




Col 7

val 7

View 6 Replies


ADVERTISEMENT

Rotate View - Show Result With 3 Columns In 1 Row

Jan 19, 2012

I have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).

No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910

No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910

View 1 Replies View Related

Specifying That Encryption Should Not 'rotate The IV

Aug 22, 2007

There has been a great deal of discussion around searching on encrypted columns.

Is there a way to 'disable' the fact that IVs and other info are changed for each set of clear text to be encrypted?

This would introduce a certain level of weakness, I know. but in my mind, security is all abour risk assessment, which includes the cost of implementing security.

To me, the cost of not being able to search on an encrypted string is higher than mitigating against the chance that someone could brute force decrypt AES 256 - if this makes sense.

Thanks,

...Andrew

View 1 Replies View Related

Rotate SQL Server Tables

Feb 3, 2003

Is there a way to rotate db tables - i.e. to pivot them?

Thanks,

Bianca

View 2 Replies View Related

How To Rotate Or Flip Table.

May 12, 2005

I have a key value pair as listed below.

Key value
------ ----------
name rob
age 30
sex male
... ...
... ...

would like to see a result set like below.

name age sex ...
---- --- ---- -----
rob 30 male ...

Note: the number of key value pairs are unknown.

View 3 Replies View Related

Multiple Columns Into Single Row -- Very Urgent

Jun 15, 2006

Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID       TYPE       VALUE1         type1      121         type2      152         type1      16   2         type2      19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID      TYPE1      TYPE2      VALUE1      VALUE21        type1       type2      12             152        type1       type2      16             19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.

View 1 Replies View Related

Sum Columns Based On A Single Column Value

Sep 10, 2014

I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.

If I have the following view:

IDLNFNCDTotal
123456MearsBenP12
123456MearsBenA2
123456MearsBenT6
234567NortonSusanP15
234567NortonSusanA2
234567NortonSusanT2
234567NortonSusanE1

I would like something like this:

IDLNFNPresentAbsentPercentage
123456MearsBen18290.0
234567NortonSusan17385.0

I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.

I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.

SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
((SELECT SUM(TotalAHD) AS Expr1
FROM SumAHDforAttndPercentages AS p

[Code] ...

View 2 Replies View Related

Multiple Columns From Single Cell

Mar 31, 2008

Running SQL2000

I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist).

This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL?





Tables, Keys, and Relationships


CREATE TABLE [dbo].[Flag] (
[FlagID] [int] NOT NULL ,
[FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StudentFlag] (
[StudentFlagID] [int] NOT NULL ,
[FlagID] [int] NULL ,
[StudentID] [int] NOT NULL ,
[FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD
CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED
(
[FlagID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD
CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED
(
[StudentFlagID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentFlag] ADD
CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY
(
[FlagID]
) REFERENCES [dbo].[Flag] (
[FlagID]
)
GO




Sample Output


StudentID, FlagType1, FlagType2, FlagType3
1, 'yes', '2', null
2, null, null, 'X'
3, null, '7', 'X'

View 4 Replies View Related

Selecting Columns From Different Tables Into A Single Table

Jan 5, 2008

I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id  (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!

View 1 Replies View Related

How To Retrieve Two Columns From Different Tables As A Single Result Set

May 16, 2008

I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.

View 6 Replies View Related

Split A Single Column Data In To 2 Columns

Jan 13, 2005

Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive

FName
John?Doe

FName LName
John Doe

thanks for the help
prit

View 7 Replies View Related

Making Single Column Value To Multiple Columns

Mar 4, 2008

I have a table which has single column like this.


REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT

I want to put this in a new table in individual columns

Col1 col2 col3 col4 col5 col6 col7 col8 col9

REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT

How to do this.
Thanks.

View 2 Replies View Related

Transact SQL :: Table Name Followed By Columns Names In A Single Row?

May 12, 2015

I am able to get a list of columns in a table from the query I have written shown below:

select sc.name ColumnNames,st.name TableName from sys.columns sc inner join sys.tables st on sc.object_id=st.object_id
order by st.name

But I am looking for the resultset with the format below:

TableName   Columns
employee      employeeid,employeename,employeesalary
order             orderid,address,price 

View 2 Replies View Related

Transact SQL :: Single Row Multiple Columns Into One Column

Jun 4, 2015

I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.

Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)

[code]....

View 10 Replies View Related

How To Return Multiple Columns As Single Column

Mar 9, 2008

I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).

View 3 Replies View Related

Reporting Services :: Rotate Textbox 45 Degrees

Apr 3, 2012

We want to rotate the textbox 45 degrees. When I use properties/writing mode, change it to "Rotate270", the normal textbox "This is not"

becomes the following

But we want to rotate it like below, is there a way to do this in BIDS? I found this thread but not working

[URL] ...

View 5 Replies View Related

Rotate Text To Flow Vertically Instead Of Horizontally

Aug 23, 2007

Is it possible in the SSRS Report Designer to rotate a textbox to run vertically, or simply to rotate the text in a textbox ? I have only seen this done within axes lables of charts, but not text boxes.

Thanks in advance,
Kenny

View 1 Replies View Related

Adding 2 Columns In A Dataset As A Single Column In Datagrid

Nov 9, 2006

hi,
i am having 2 columns in a table in a dataset.
i want to add those two columns and bind the resultant total as a single column to the datagrid.
is it possible.
if yes, how o acheive this?
please help me.
thanks in advance.
muppidi.

View 1 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows And Columns Into Single Row

Apr 25, 2014

I have this query

SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code

and the output

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10

How can I modify the query to come up with output below,

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10

View 9 Replies View Related

T-SQL (SS2K8) :: How To Retrieve Columns Name As Single Row From Table In Dynamic Way

Dec 27, 2014

I need to retrieve columns names(instead of select * from) as single row from table in dynamic way.

i m using following query.

its working fine while using from selected database. but its not working when i m running from different database.

DECLARE @columnnames varchar(max)
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames

I need to pass database name dynamically like using by variable.

Is this possible to use variable after from clause. or any other methods?

eg:

DECLARE @columnnames varchar(max)
DECLARE @variable='db_month11_2014'
select @columnnames = COALESCE(@columnnames,'')+column_name+',' from @VARIABLE.INFORMATION_SCHEMA.COLUMNS(nolock)
where table_name='table_20141224'
select @columnnames

View 9 Replies View Related

SQL Server 2008 :: Return A Single Table With Three Columns

Jan 26, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23

The results should be ordered by DateChangedOn.

View 6 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

How Can I Combine Values Of Multiple Columns Into A Single Column?

Oct 8, 2007



Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888

And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888

where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?

View 1 Replies View Related

Reporting Services :: How To Rotate Text 180 Degrees Without Using Images

Oct 13, 2011

I'm currently working on a report and have all the requirements taken care of save one. I need to flip my text 180 degrees. I have seen how to do this by making an image on the fly and then rotating the image, etc. And that is what we previously had in place.

Unfortunately the reports are getting so large (20 images on a page, 150 pages for the report) that the reports will not print in their entirety through Citrix and cuts off after about 120 pages.

I also tried using upside down fonts which won't work as they flip the letter on a X axis as opposed to rotating them along a pivot. Sadly we can't reduce the number of occurrences per page or decrease the page count. So those are not options. They were the first things I asked about.

View 6 Replies View Related

T-SQL (SS2K8) :: Return Single Row For Matching Columns Based On 3rd Column

Sep 3, 2014

I have data:

Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1

Essentially if Ticket and User are the same I just want the min priority returned.

SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1

I've tried partition and rank but can't get it to return the right output.

View 5 Replies View Related

T-SQL (SS2K8) :: Multiple Databases - Return A Single Table With Three Columns

Jan 13, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23

The results should be ordered by DateChangedOn.

View 4 Replies View Related

SQL Server 2012 :: Splitting A Single Column Into Multiple Columns?

Mar 3, 2015

I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.

For Eg:

Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L

View 9 Replies View Related

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

Oct 28, 2015

I have the following results:

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

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

View 9 Replies View Related

Reporting Services :: Axis-X Labels Rotate 90° Doesn't Work

Jun 14, 2012

i would like to show all Labels available in a Chart and rotate the Labels 90°. How can i do that ? I changed the Propertie "Interval" to 1, "LabelsAngle" to 90, "LabelsAutoFitDisabled" to true in my Chart Axis.

View 12 Replies View Related

SQL Server 2012 :: Insert Values In A Single Table With Four Columns From 4 Different Sources?

Jan 30, 2014

I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other

View 3 Replies View Related

SQL 2012 :: Rolling Up Multiple Row And Columns Into A Single Cell NULL Error

Jul 28, 2015

So I have a problem with combining cells: some of the cells are blank and some of the cells have NULL value.

The combined blank cells return perfect.

But the combined cells in which 1 or more contains NULL will return as NULL

0-4139-00-01OPTIONAL EQUIPMENT 139 - A -
0-4139-00-02NVG EQUIPMENT 139 - A -
0-4139-00-03NVG EQUIPMENT 139 - A -
0139-01-01BRAKE RESERVOIR NULL

A part of the script which i use is:

STUFF((SELECT ', ' + i.ac_type + ' - '
+ i.range_or_single+ ' ' + ' - ' + i.serialno_to
FROM msc_item_effectivity i
inner JOINmsc_item h
ONb.taskcard_verno_i = h.taskcard_verno_i
WHERE h.itemno_i = i.itemno_i
FOR XML PATH('')), 1, 1, '')

i.ac_type = 139
i.range_or_single = A
i.serialno_to = blank or NULL

I want the result of the blank value to be the same as the NULL value.

0139-01-01BRAKE RESERVOIR 139 - A -

View 2 Replies View Related

Reporting Services :: Concatenate 24 Columns With Semicolon Delimited Into Single Column

Sep 17, 2015

How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....

View 2 Replies View Related

Data Flow Task - Multiple Columns From Different Sources To A Single Table

Dec 19, 2006

Hi:


I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks


MA2005

View 9 Replies View Related







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