March 1, 2011 at 1:25 pm
Need help on t-sql query which turns rows into columns.
DML and DDL as follows:
Create Table #Test
(
[testId] [int] NULL,
[testVersion] [int] NULL,
[AdminType] [nvarchar](10) NULL,
[StatusId] [nvarchar](30) NULL,
[StatusDate] [datetime] NULL
)
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'CAB','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'RM','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'Test','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'Env','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'TP','Accept','03/01/2011')
Select * From #Test Where testId = 1 and testVersion = 2
out put is
----------
testId testVersionAdminTypeStatusIdStatusDate
12CABAccept2011-02-28 00:00:00.000
12RMAccept2011-02-28 00:00:00.000
12TestAccept2011-02-28 00:00:00.000
12EnvAccept2011-02-28 00:00:00.000
12TPAccept2011-03-01 00:00:00.000
Expected Format
testIdtestVersionCABRMTestEnvTP
12Accept Accept AcceptAccept Accept
Hope am clear on my requirement.
TIA...
March 1, 2011 at 1:27 pm
You'll want to take a look at the Pivot and Unpivot operators in T-SQL.
Honestly though, I do everything I can to avoid doing that in the database or in T-SQL. It's just not that good at it. There are much better tools for pivoting data, like SSRS or Excel.
- 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
March 1, 2011 at 1:38 pm
I understand your suggestion regarding not to use in t-sql. But few things which are out of our control makes us to use these inside t-sql.
I appreciate if you can send me the query...
March 1, 2011 at 1:59 pm
GSquared answered your question. 😀
As well as it seems you have the answer in your own signature under "Cross Tabs and Pivots" :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 2, 2011 at 3:28 am
SELECT testId,testVersion,
MAX(CASE WHEN AdminType='CAB' THEN StatusId END) AS CAB,
MAX(CASE WHEN AdminType='RM' THEN StatusId END) AS RM,
MAX(CASE WHEN AdminType='Test' THEN StatusId END) AS Test,
MAX(CASE WHEN AdminType='Env' THEN StatusId END) AS Env,
MAX(CASE WHEN AdminType='TP' THEN StatusId END) AS TP
FROM #Test
GROUP BY testId,testVersion;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537March 2, 2011 at 5:56 am
Mark-101232 .. You rock...I was struggling with pivot..and you gave a solution by not using PIVOT..Thanks a lot mark....
March 2, 2011 at 6:49 am
NewBeeSQL (3/2/2011)
Mark-101232 .. You rock...I was struggling with pivot..and you gave a solution by not using PIVOT..Thanks a lot mark....
As a suggestion, take the time to read the article about "Cross Tabs and Pivots" located in your signature line so you understand why the solution Mark posted works. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply