October 26, 2011 at 8:07 am
Hi,
Hi,
I have data in following format from my variable table
ID ! TEXT
---------------------------
1 ! ABCD
1 ! EFGH
how can I make
ID ! TEXT 1 ! TEXT 2
---------------------------
1 ! ABCD ! EFGH
Thanks
October 26, 2011 at 8:19 am
How does the table tell the query which one goes first, which one goes second? Will there ever be a third, fourth, et al?
Or does the sequence not matter?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 9:21 am
Thanks for your reply. yes there can be third, fourth, ....
TEXT (ABCD or EFGH)is basically concatenation of 'datetime + TEXT '
so ABCD = '8/20/2010 10:00 AM'
EFGH = '8/20/2010 11:00 AM'
for each ID the date part is going to be common
so if ID =1 then ABCD ,EFGH , IJKL....will all have date part '8/20/2010' common. Only the time will change
------------
I was able to do it in alternate way-- by assigning Rank /row_partition like below, but it would be great if anyone can advise on how pivot () can be used.
--------------------------------
DECLARE @second_table Table
( Rank int
, ID int
, text varchar(1000)
)
INSERT INTO @second_table
SELECT RANK() over (partition by ID order by text ) as Rank,a.* from
(SELECT * from @first_table ) as a
--------------------
DECLARE @testing Table
( ID int
, TEXT1 varchar(1000)
, TEXT2 varchar(1000)
, TEXT3 varchar(1000)
, TEXT4 varchar(1000)
)
INSERT INTO @testing
SELECT
ID
, max(CASE WHEN rank =1 THEN a.text ELSE '' END) AS 'TEXT1'
, max(CASE WHEN rank =2 THEN a.text ELSE '' END) AS 'TEXT2'
, max(CASE WHEN rank =3 THEN a.text ELSE '' END) AS 'TEXT3'
, max(CASE WHEN rank =4 THEN a.text ELSE '' END) AS 'TEXT4'
FROM @second_table
GROUP BY ID
----------------------------------------------------------------------------------
October 26, 2011 at 9:24 am
The way you did is just fine.
The T-SQL Pivot function is nearly useless, in my opinion. Don't bother with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 9:29 am
GSquared (10/26/2011)
The way you did is just fine.The T-SQL Pivot function is nearly useless, in my opinion. Don't bother with it.
How come?? Never bothered to learn it but now I may never will!
October 27, 2011 at 6:08 am
Ninja's_RGR'us (10/26/2011)
GSquared (10/26/2011)
The way you did is just fine.The T-SQL Pivot function is nearly useless, in my opinion. Don't bother with it.
How come?? Never bothered to learn it but now I may never will!
Check the documentation: http://msdn.microsoft.com/en-us/library/ms177410.aspx
In order to build a Pivot statement, you have to list all possible values for the rows, as columns. Simple if your data is static or only has a few possible values. Useless if you don't know the values before you write the query.
So, the only way to make it useful is to first query the distinct values, then use that to write a dynamic Pivot statement. That means you're doing the same thing you had to do in SQL 2000 to create a dynamic pivot, but you're using much more complex syntax to do it.
So, useless.
Unpivot is useful, because you can easily know what your column headers are before you write the query, and they won't change as data is modified in the table. Pivot is the useless one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2011 at 6:41 am
GSquared (10/27/2011)
Ninja's_RGR'us (10/26/2011)
GSquared (10/26/2011)
The way you did is just fine.The T-SQL Pivot function is nearly useless, in my opinion. Don't bother with it.
How come?? Never bothered to learn it but now I may never will!
Check the documentation: http://msdn.microsoft.com/en-us/library/ms177410.aspx
In order to build a Pivot statement, you have to list all possible values for the rows, as columns. Simple if your data is static or only has a few possible values. Useless if you don't know the values before you write the query.
So, the only way to make it useful is to first query the distinct values, then use that to write a dynamic Pivot statement. That means you're doing the same thing you had to do in SQL 2000 to create a dynamic pivot, but you're using much more complex syntax to do it.
So, useless.
Unpivot is useful, because you can easily know what your column headers are before you write the query, and they won't change as data is modified in the table. Pivot is the useless one.
Exactly why I didn't bother learning it :-D.
October 27, 2011 at 7:35 am
Thanks all !!. I will play around with pivot for this situation in my spare time.
Appreciate your help.
November 18, 2011 at 10:15 am
I was thinking of crosstabs the other day, and ran across an article written by Keith Fletcher on Simple-Talk.com:
http://www.simple-talk.com/content/print.aspx?article=369
That's the printer-friendly version.
He developed a crosstab stored procedure that works very similarly to the MS Access crosstabs. It requires a little more thought and care, and I haven't tried it yet, but looks like what you might want.
Jim
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply