PIVOT FOR DYNAMIC ROWS

Question

Post reply

PIVOT FOR DYNAMIC ROWS

subasuga

Mr or Mrs. 500

Points: 510
More actions
October 8, 2011 at 4:50 am

#249644

Hi..
I have a table whose number of rows is determined only at run time. The dataset is divided into group of 5 (represented by sampleNo)
Like this...
1......AAAA......Sample1
2......SSSS.......Sample1
3......HHHH.......Sample1
4......UUUU.......Sample1
5......IIIIIII.......Sample1
6......BBBB.......Sample2
.........
10.....XXXX......Sample2
11......CCCC.......Sample3
............
15.......DDDD.......Sample3
.......
n........TTTT........SampleN
I dont know how many samples will be present in the table. I want to create a pivot so that i shall have a table like
RowNum......Sample1......Sample2.....Sample3.....Sample4.....SampleN
1...............AAAA..........BBBB..........CCCC....................................
2...............SSSS.....................................................................
3...............HHHH.....................................................................
4...............UUUU.....................................................................
5...............IIIIIII.....................................................................
At any point of time i will have only 5 rows; but N number of columns.
How to go about it?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 1

The following article will tell you how to do it...

http://www.sqlservercentral.com/articles/Crosstab/65048/

If you want a coded reply, the following article will tell you how to get one...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

subasuga (10/8/2011)
Hi..
I have a table whose number of rows is determined only at run time. The dataset is divided into group of 5 (represented by sampleNo)
Like this...
1......AAAA......Sample1
2......SSSS.......Sample1
3......HHHH.......Sample1
4......UUUU.......Sample1
5......IIIIIII.......Sample1
6......BBBB.......Sample2
.........
10.....XXXX......Sample2
11......CCCC.......Sample3
............
15.......DDDD.......Sample3
.......
n........TTTT........SampleN
I dont know how many samples will be present in the table. I want to create a pivot so that i shall have a table like
RowNum......Sample1......Sample2.....Sample3.....Sample4.....SampleN
1...............AAAA..........BBBB..........CCCC....................................
2...............SSSS.....................................................................
3...............HHHH.....................................................................
4...............UUUU.....................................................................
5...............IIIIIII.....................................................................
At any point of time i will have only 5 rows; but N number of columns.
How to go about it?

Question :

Where do the following rows appear in your expected results:

10.....XXXX......Sample2

15.......DDDD.......Sample3

n........TTTT........SampleN

___________________________________________________________

As already pointed out to you...to be able to provide an answer to your question, it would really help us to help you, if you could please provide some further details, in the form of DDL / sample data / expected results....(you will see this as a common request in the forums.). The people who respond on SSC are all volunteers and do not have the time to reconstruct your data .(and, as far as I know ..are not mind readers either :-)).

If the data that you can provide will easily allow them to attempt a solution, then chances are that they will....otherwise they are likely to move on .. and quickly !!

Firstly create some sample table(s) and populate with data that fully represent the issue you are asking about.

If you need to obfuscate the data for confidentiality purposes…then do it now….but please do not over simplify the data…on many occasions people try to provide a “simple” view that only masks potential problems further into the problem solving.

Then you need to post a table(s) creation script and the required insert script(s) for your data...to do this here are some possible methods.

___________________________________________

use the built in functionality in 2008/2008R2

good article here by Eric Johnson http://sqlblog.com/blogs/eric_johnson/archive/2010/03/08/script-data-in-sql-server-2008.aspx

___________________________________________

A well tried, respected and trusted method can be found here Jeff Moden http://www.sqlservercentral.com/articles/Best+Practices/61537/

defintely worth reading to explain the reasons why we ask for scripts

____________________________________________

An alternative is to use a 3rd party addin such as http://www.http://www.ssmstoolspack.com/%5B/url%5D%5B/b%5D

this has a whole host of useful features...and is free if you decide not to donate

____________________________________________

Once you have your scripts, you need to add these as SQL code in your post/reply. To do this insert your code between the “IFCode shortcuts” ..

code="sql" and /code (both of these are contained in square brackets [] )

...select these from the message pane on the left of the "post reply" window and insert your copied code between them.

This will then allow people to easily "consume" your code by copying and pasting from your question directly into SSMS...get the same data that you have and hopefully provide you with a tried and tested solution....

Please remember to provide your expected results based on the data you have provided

As an example...here is a script that creates a table and inserts the data from your original post.

USE [tempdb]

GO

CREATE TABLE [dbo].[TBL_1](

[COL_1] [varchar](50) NULL,

[COL_2] [varchar](50) NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[TBL_1]([COL_1], [COL_2])

SELECT N'AAAA', N'Sample1' UNION ALL

SELECT N'SSSS', N'Sample1' UNION ALL

SELECT N'HHHH', N'Sample1' UNION ALL

SELECT N'UUUU', N'Sample1' UNION ALL

SELECT N'IIII', N'Sample1' UNION ALL

SELECT N'BBBB', N'Sample2' UNION ALL

SELECT N'XXXX', N'Sample2' UNION ALL

SELECT N'CCCC', N'Sample3' UNION ALL

SELECT N'DDDD', N'Sample3' UNION ALL

SELECT N'TTTT', N'SampleN'

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day