reverse the table

Question

Post reply

reverse the table

love.handsome

SSC Journeyman

Points: 89
More actions
November 20, 2008 at 3:11 am

#196099

Hi All
I have a table Table1 as
1 a
1 b
1 c
1 d
2 e
2 f
2 x
2 y
2 z
3 q
4 a
4 b
4 c
4 d
I want to show the results in this form
1 a b c d -
2 e f x y z
3 q - - - -
4 a b c d -
can any one help me related to this problem

Viewing 9 posts - 1 through 8 (of 8 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

ChiragNS

One Orange Chip

Points: 26137

November 20, 2008 at 3:39 am

#900950

Check out PIVOT operator

"Keep Trying"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hello

Here's an excellent article which describes various solutions to your problem.

http://www.sqlservercentral.com/articles/T-SQL/63681/

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

love.handsome SSC Journeyman Points: 89 More actions · Answer 3

I have tried PIVOT but their is no limit for rows

it may be

1 a

1 b

1 c

1 d

2 e

2 f

2 x

2 y

2 z

3 q

4 a

4 b

4 c

4 d

5 e

5 f

5 x

5 y

5 z

6 q

6 a

7 b

7 c

7 d

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

There just really needs to be a limit to the number of columns. Pivot will probably not work for you, but this can be done relatively easy.

Here is what I need from you to give you an answer:

1) The number of columns you want returned

2) Post some useable sample data for me. Here are some suggestions on how: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

love.handsome SSC Journeyman Points: 89 More actions · Answer 5

Hi

As I told earlier their is no limit for no of columns

can you suggest me some solution related to this

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

Earlier, you said there was no limit to the number of rows, not the number of columns.

With no limit to the number of columns, the only T-SQL solution will be to use dynamic SQL to generate a SQL statement with the number of columns based on the data in the table. This is typically a bad idea.

This type of data re-formatting is something best handled on the client or application end rather than in T-SQL.

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 7

I want to show the results in this form
1 a b c d -
2 e f x y z
3 q - - - -
4 a b c d -

Dynamic sql can certainly help generate the desired results you seek, but I'm wondering what your columns will be called. Based on what you've presented, a generic approach that labels the columns as Col1, Col2... would be fairly easy.

Below is the sql that will transpose your table:

IF OBJECT_ID('TEMPDB..#DATA') IS NOT NULL

DROP TABLE #DATA

CREATE TABLE #DATA

(

KEYID INT

,TXT_VAL VARCHAR(1)

)

INSERT #DATA (KEYID, TXT_VAL)

SELECT 1,'a' UNION

SELECT 1,'b' UNION

SELECT 1,'c' UNION

SELECT 1,'d' UNION

SELECT 2,'e' UNION

SELECT 2,'f' UNION

SELECT 2,'x' UNION

SELECT 2,'y' UNION

SELECT 2,'z' UNION

SELECT 3,'q' UNION

SELECT 4,'a' UNION

SELECT 4,'b' UNION

SELECT 4,'c' UNION

SELECT 4,'d'

ALTER TABLE #DATA

ADD AUTONUM INT IDENTITY(1,1)/* AUTONUM ENSURES ALL ROWS HAVE A UNIQUE IDENTIFIER */

,COL_ID INT/*COL_ID WILL IDENTIFY THE ROWNUMBER WITHIN A KEYID */

GO

UPDATE #DATA

SET COL_ID = (SELECT COUNT(*) FROM #DATA B WHERE B.KEYID =A.KEYID AND B.AUTONUM<=A.AUTONUM)

FROM #DATA A

/* DISPLAY THE MODIFIED TABLE */

SELECT * FROM #DATA

/* DECLARE A VARIABLE TO HOLD THE DYNAMIC SQL STRING */

DECLARE @sql NVARCHAR(2000)

SELECT @sql = N'SELECT KEYID' +CHAR(10)

/* LOOP TO BUILD THE NUMBER OF COLUMNS */

SELECT@sql = @sql + N', COL' + CONVERT(NVARCHAR(3),A.COL_ID) +

+ N'=MAX(CASE WHEN COL_ID =' + CONVERT(NVARCHAR(3),A.COL_ID) + N' THEN TXT_VAL ELSE ''-'' END)'

+CHAR(10)

FROM(

SELECT DISTINCT COL_ID

FROM#DATA

) A

SELECT @sql = @sql + N'FROM #DATA '

+ CHAR(10) + N'GROUP BY KEYID'

+ CHAR(10) + N'ORDER BY KEYID'

PRINT @sql /* THIS LINE MERELY DISPLAYS THE SQL QUERY TO BE EXECUTED. */

/* DISPLAY THE TRANSPOSED RESULTS*/

EXEC SP_EXECUTESQL @sql

/*************************************

**END OF SCRIPT**

*************************************/

Hope that helps

--Pete

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 8

Just curious... why? If you have no limit to the number of columns, then any relative table you can create will not be useable, unless by another dynamically created object or script. If you are just trying to view the data... many reporting packages, such as SSRS, Crystal (Business Objects), and even Excel (written out in a variety of ways) have pivot table options. No offense, but it sounds like, unless you are trying to create a limited "info only" query in SSMS or do not have any decent reporting software you are trying to do more work than is necessary.

FYI, I do have a few stored procedures that for various reasons has had to allow for "any number of columns"; I'm not trying to be a hypocrite, I just want to make sure your not spinning your wheels unnecessarily.