SQL Query: Rows To Columns

Feb 6, 2008


Hi All,

I have a sample table as below:
(All columns do not have to have unique values)
Column n is int, the rest are varchar
[First row is the column Names]

n | t1 | t2 | t3 | t4
1 | a | b | c | d
3 | e | w | a | t
3 | w | q | u | k
2 | g | w | q | n
2 | a | b | s | a
5 | n | a | w | h


I need to create a query or some how create a way to get the data in the form of the below table:

n | t1 | t2 | t3 | t4 | t11 | t21 | t31 | t41
1 | a | b | c | d |
3 | e | w | a | t | w | q | u | k
2 | g | w | q | n | a | b | s | a
5 | n | a | w | h


What that means is that I have a huge table (~6000 rows) that has the column 'n' above (int type) which can have rows that start with the same number (such as number 3 & 2 above). I need these duplicate rows of numbers to be converted/transformed to columns alongside each other. This would create 1 entry for each type of number but have the 4 columns of t1, t2, t3, t4 'added' on as extra columns in the new table with a different column name ofcourse.
So what that would mean is that if there were 3 rows of number n=2 then would have 3x4=12 columns, plus the 'n' column and so forth.

I am not sure on how to start the query for this.
What I have thought is maybe using a cursor and going through the table. But not sure how I could do that really.
The other is using a Pivot in a SELECT statement. But that would not work since the columns can grow indefinetely depending on how many of the same 'number' row there is!

Any ideas on this? Thanks a bunch !!!


(running SQL 2005)

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Query To List Rows Into Columns?

Aug 10, 2015

I have requirement to list each row into column. I have tried with pivot query but unable to get it. I am using SQL Server 2008 database.

Here is the sample data:

CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,

[Code].....

View 3 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

SQL Server 2012 :: Pivot Rows And Columns In The Same Query?

Mar 26, 2015

I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.

The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.

View 2 Replies View Related

Table Imported From Excel - Query For Columns Into Rows

Apr 3, 2013

I have a table imported from excel(5 var fix and 5 val fix) like:

Var1 val1 var2 val2 var3 val3 var4 val4 var5 val5
Color Red Size 4 Height 2inch NULL NULL NULL NULL
Color Red Size 5 Height 2inch NULL NULL Length 1 cm
Color Red Size 4 Height 3inch NULL NULL NULL
Color Black Size 6 Height 1inch NULL NULL NULL NULL

I need the output as

Var --- Val
Color --- Red,Black
Size --- 4,5,6
Height 1inch,2inch,3inch
Length 1 cm

Var or val can be either NULL or white space. I cannot use a function.

View 5 Replies View Related

Query To Change Rows Into Columns And Vice Versa

Nov 13, 2007

I would like to change rows into columns and columns to rows for the query output table




If the query output is shown the table below (including the column names in the first row).

A B C D E
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 b3 c3 d3 e3
a4 b4 c4 d4 e4
a5 b5 c5 d5 e5

The table needs to be converted to (the rows become columns and columns become rows).

A a1 a2 a3 a4 a5
B b1 b2 b3 b4 b5
C c1 c2 c3 c4 c5
D d1 d2 d3 d4 d5
E e1 e2 e3 e4 e5



Thanks
KK

View 12 Replies View Related

MDX Query Builder Missing Rows And Columns Areas

Feb 6, 2008

I was using the MDX Query Builder to create MDX queries for a SSRS report. I'm not sure what happened, but when I tried to create another dataset against the cube, the "Drop Column Fields Here" and "Drop Row Fields Here" areas were no longer available for me to drop attributes onto.

I have restarted VS, rebooted, you name it, I've tried it (short of re-installing). Has anyone encountered this and how did you "fix" it.

BTW: In order to continue working, I decided to use ProClarity to build the MDX for me and when I tried to paste it into the MDX editor, I get the following error: "The query cannot be prepared: The query must have at least one axis. ..". So, as I've seen from other posts, you can't use "any" MDX in the MDX Query Builder.

Malinda

View 1 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

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

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows

Aug 5, 2014

I managed to transpose rows into columns.

;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference

[code]...

But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..

ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view

ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

View 6 Replies View Related

Turn Columns Into Rows And Rows Into Columns

Jan 24, 2008

I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.
Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount
123, Grenadine, 100.00, 75.00, 15.50, 2.00
456, Lime Juice, 120.00, 81.00, 17.25, 2.00

There could be 1 item or 4000 items.

What I want to see is.

Item # - 123, 456
Description - Grenadine, Lime Juice
CaseSalePrice - 100.00, 120.00
Cost - 75.00, 81.00
BottleSalePrice - 15.50, 17.25
Discount - 2.00, 2.00

What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.

I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.

The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.

I am all out of creative ideas, any help would be appreciated.

View 6 Replies View Related

SELECT Query - Different Columns/Number Of Columns In Condition

Sep 10, 2007

I am working on a Statistical Reporting system where:


Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by


different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1


Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2


Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:


Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups

Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:


How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)

Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.

Thanks for all your help!

View 1 Replies View Related

Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate

Nov 8, 2007

Hi All,

I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.

But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.

There are indexes in the tables.

Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.


But nothing is improving

View 7 Replies View Related

Columns Vs. Rows

May 10, 2007

hi,I'm building a multi-lingual website In my database tables I have, in some of them, a column with the Language, because some of the columns depend on what language the user wants to see the site.My question is:  what is better? have that column and consequently two row (for two languages) with repeated column information? or have two column within a row with the language specification?e.g. table: id, description, price(1) With language:id,description, price, language='EX' id,description, price, language='EN' vs.(2)  id, descriptionEN,descriptionEX,price   if I have 500 products in 1 whould result in 1000 entriesin 2 just 500 results can anyone tell me a diference/advantage between the two approachs?thanks in advance.  

View 1 Replies View Related

Rows As Columns

Oct 6, 2005

is it possible to write a query so that we can have all rows of one column in a  single columnTIA

View 1 Replies View Related

Rows Become Columns Help!

Dec 5, 2005

I am building a calendar table for the most reason four weeks activitis and I have had a temp table data in table A (See my attached file) and I want to
make it as the format in table B as final. How to convert it? Please help!

Thank you!

Suin

View 2 Replies View Related

Columns To Rows And A Sum

Nov 19, 2013

I am using SQL 2008. I have a database called ISCmetrics and a table called Meeting, the table meeting has 5 columns id

( int),TEAMNAME (varchar),DATECOMPLETE (varchar),STATUSNAME (varchar),STATUSLEVEL (varchar)

We have around 20 different team names , and every team enters data into the database every day, and we have around 7 Different STATUSNAME they are always the same , the STATUSLEVEL is always a 1 or a 0 , so TEAMA enters a value every day for each of the 7 STATUSNAME, and the value is either a 1 or a 0 .

The output for a Select * from ISCMetricslooks like this

id TEAMNAME DATECOMPLETE STATUSNAME STATUSLEVEL
1 TeamA 20131022 STATCONTACT 1
2 TeamA 20131022 STATACTION 1
3 TeamA 20131022 STATABC 1

4 TeamB 20131022 STATCONTACT 1
5 TeamB 20131022 STATCTION 0
6 TeamB 20131022 STATABC 1

7 TeamA 20131021 STATCONTACT 0
8 TeamA 20131021 STATACTION 1
9 TeamA 20131021 STATABC 0

10 TeamB 20131021 STATCONTACT 1
11 TeamB 20131021 STATACTION 1
12 TeamB 20131021 STATABC 1

What i am trying to achieve is this, i want the teams in one column then a column for each of the dates , and then sum of the STATUSLEVEL in each row as shown below for the day and team .....

TEAMNAME 20131022 20131021
TeamA 3 1
TeamB 2 3

View 20 Replies View Related

Rows Become Columns

Jan 18, 2007

hello all,
i am trying to create a view from a table that will keep track of the time between each stage of tasks given. take a look at the data below:


progressID taskIDstage status theDate
------------------------------------------------------------------------
1407525 1091657In Progress Logged 2006-11-16 10:00:24.000
1407526 1091657 In Progress Inprogress 2006-11-16 12:08:59.036
1407214 1091657In Progress Resolved 2006-11-16 14:15:48.000
1407220 1091657Closed Solved 2006-11-16 14:36:05.000


i would like to be able to have just one row per task ID showing the difference between the stages, as shown below . only the 2nd column is a date, the rest are are hours (datediff) between the stage and its preceeding stage :

taskID Logged InProgress Resolved Solved
1091657 2006-11-16 10:00:24.000 2.08 2.07 0.21


is it possible to achieve such a transformation using views and a number of select statements (i.e no dts)? all assistance will be highly appreciated.

regards,

ptah

View 2 Replies View Related

Rows To Columns

Jun 9, 2007

Afternoon

I am trying to write a query that will return the columns: year/ month, each status type (unknown how many types there are)

Each row is a different join year/ month

Each cell has the count of users that joined in that rows year/ month and currently have the status of the column.

At the moment I have the following query:

SELECT [remortgage-status].status, COUNT(1) AS CountTotal, YEAR([remortgage-log].datetime) AS Year, MONTH([remortgage-log].datetime) AS month FROM [remortgage-status] INNER JOIN [remortgage-log] ON [remortgage-status].clientid = [remortgage-log].clientid WHERE ([remortgage-log].action = N'Joined') GROUP BY [remortgage-status].status, YEAR([remortgage-log].datetime), MONTH([remortgage-log].datetime)

The problem is that each different status is a new row rather than each status being a column.

What do I need to do to correct this? - I dont know all the different possible statuses at this point

View 9 Replies View Related

Columns Into Rows

Feb 29, 2008

Hi,
I've a table called months with one column month. The result set will be
month
=====
Jan
Feb
Mar
...

Now i want to convert those values as rows. Means,

Jan Feb Mar
===========

Suggest me a solution for the above problem.


Thanks
Somu

View 2 Replies View Related

Rows As Columns

Jul 20, 2005

I know this is a self join, but I can't remember exactly how it goes.Could someone help me out?create table A{int idA,varchar(30) dataA}create table B{int idB,varchar(30) dataB}create table A_B{int idA references A(idA),int idB references B(idB)}insert into A values(1, "foobar")insert into A values(2, "barfoo")insert into B values(1, "a")insert into B values(2, "b")insert into B values(3, "c")insert into B values(4, "d")insert into B values(5, "e")insert into B values(6, "f")insert into B values(7, "g")insert into B values(8, "h")insert into A_B values (1, 1)insert into A_B values (1, 2)insert into A_B values (1, 3)insert into A_B values (1, 4)desired resultsfoobar a b c dThanks,-- Rick

View 9 Replies View Related

Columns To Rows

May 26, 2006

Lets say I have the following rows..

ID,Net,Gross,Total
1 ,25.00,55.00,100.00
2,35.00,65.00,250.00

What would be the best way to do this...

ID, Description, Value
1, Net, 25.00
1, Gross,55.00
1, Total, 100,00
2, Net 35,00

I was using the multicast and doing a bunch of derived columns but it seems like there should be a eaiser way to do this.

Any help would be appreciated, thanks.

Mardo

View 1 Replies View Related

Rows As Columns

Mar 24, 2008

Hi


i have rows as follows


1 col1 value1

1 col2 value2

1 col3 value3

2 col1 value4

2 col2 value5

2 col3 value6


need result as

id col1 col2 col3

1 value1 value2 value3

2 value4 value5 value6


How to achieve this?


I am trying to achieve this using Pivot, but it is aggregating.

View 8 Replies View Related

XML Columns To Rows

Mar 14, 2007

I have a large amount of data in an XML file. The file is very simple. Off of the root node are category nodes and each category node has many detail nodes. The detail node consist of the node name and the node value. Rather than map the detail to matching named fields in a single record, I would like to send the detail node data to individual data records where the category code, node name and node value are the only fields in the record. For example,

Starting with

<Category>

<Code>001</Code

<Details>

<DetailCode123>75.87</DetailCode123>

<DetailCode728>12.98</DetailCode728>

<DetailCode387>55.72</DetailCode387>

</Details>

</Category>

I want to be able to pick up the 001 code for the category and then transform the above data into 3 separate table records with the following fields (CatCode, DetailCodeName, DetailValue).

001, DetailCode123, 75.84

001, DetailCode728, 12.98

001, DetailCode387, 5572

Does this sort of transform exist? If not, I will have generate my own parsing script. Thought it was worth asking before doing the additional work.

Thanks

View 2 Replies View Related

Rows Into Columns

Aug 28, 2006

I am converting rows into columns :

Schedule_S1 contains more than 1 rows with different FROM_DATE and TO_DATE

The query should merge 2 rows into 1 row with 2 different FROM_DATE AND TO_DATE

The following query works fine when there are more than 1 records. When there is only 1 record, it should still return the record (with FROM_DATE_2 and TO_DATE_2 as nulls). Thanks for any help.

SELECT
ml1.P_ID,
ml1.S_ID,
ml1.CURRENT_DAY,
ml1.FROM_DATE AS "FROM_DATE_1",
ml1.TO_DATE AS "TO_DATE_1",
ml2.FROM_DATE AS "FROM_DATE_2",
ml2.TO_DATE AS "TO_DATE_2"
FROM
Schedule_S1 ml1 JOIN
Schedule_S1 ml2 ON
ml2.FROM_DATE > ml1.FROM_DATE
WHERE
ml1.P_ID = 59014 AND
ml1.S_ID = 25691 AND
ml1.CURRENT_DAY = 3 AND
ml2.P_ID = 59014 AND
ml2.S_ID = 25691 AND
ml2.CURRENT_DAY = 3
ORDER BY
ml1.FROM_DATE

View 9 Replies View Related

Rows And Columns

Jan 3, 2007

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?

View 4 Replies View Related

Columns From Rows

Mar 23, 2006

Hello,



I trying to make columns from rows? How do I do that? I am using SQL Server 2000.



I thank in advance anyone capable of helping me.



E. Woods

View 3 Replies View Related

Rows Into Columns And Columns Into Rows

Aug 14, 2007

I have data in a table. I want the values in the rows to place in columns and columns into rows.
Eg:-A table. It consists of three columns and three rows.

name id dept
a 1 x
b 2 y
c 3 z

I want the resultant table should look like this

a b c
1 2 3
x y z

Whether it's possible ?

View 1 Replies View Related

Mapping Columns To Rows

Mar 13, 2008

Hi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
 

View 3 Replies View Related

Converting Rows Into Columns

Sep 20, 2004

Hi

How can one convert rows into columns (or all rows in one column as a single row, except each row in its own column), either by using a temperary table or just in a select statement?

View 2 Replies View Related

Rearrange Rows And Columns

Apr 14, 2008

I have a table with three columns:
AcctNbr, Type, CodeValue

Listed below is an example of the database.

Code:


AcctNbr Type CodeValue
1 MAILCODE 99
2 MAILCODE 99
3 MAILCODE 99
4 MAILCODE 90
4 MAILCODE 99
4 SEG1 O
5 MAILCODE 99
6 MAILCODE 99
7 MAILCODE 99
8 MAILCODE 99
9 MAILCODE 99
10 MAILCODE 90
11 MAILCODE 99
12 MAILCODE 99
13 MAILCODE 99
14 MAILCODE 99
15 LIST DS1
15 MAILCODE 99



There are multiple Type's for some AcctNbr's and what I want to do is
run a query on the database so that if the AcctNbr has multiple Type's
and CodesValue's it takes them and creates new columns like so:


Code:


AcctNbr MailCode_90 MailCode_99 SEG1
4 90 99 O


So on and so forth. There are multiple Type's and multiple codes that
I need to do this with for each account number. If someone could give
me a base code to try I could start somewhere. I am an SQL novice.
Thanks.

Josh

View 11 Replies View Related

Transpose Rows Into Columns

Oct 22, 2007

Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table

ID type
1 A
1 B
2 A
2 C
2 D
etc,....




I want to tranpose

to some like

ID type
1 A,B
2 A,C,D
etc,...


or

ID type1 type2 type3
1 A B
2 A C D

etc,....


Really appreciate any input.

View 3 Replies View Related







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