Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-04 : 15:14:23
|
Hello Gurus,I need help with following query.-- table structure with sample datadeclare @table table (id int,name varchar (100),value varchar (100))insert into @tablevalues (1,'brand','apple')insert into @tablevalues (1,'category','phone')insert into @tablevalues (1,'carrier','at&t')insert into @tablevalues (1,'carrier','sprint')insert into @tablevalues (1,'color','white')insert into @tablevalues (1,'carrier','verizon')insert into @tablevalues (1,'model','iPhone5')insert into @tablevalues (1,'screen','4')insert into @tablevalues (1,'processor','A6')insert into @tablevalues (1,'color','black')select * from @table-- current outputid name value1 brand apple1 category phone1 carrier at&t1 carrier sprint1 color white1 carrier verizon1 model iPhone51 screen 41 processor A61 color black-- expected outputid brand category carrier carrier color carrier model screen processor color1 apple phone at&t sprint white verizon iPhone5 4 A6 blackThanks. |
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-04 : 15:42:11
|
Ultimately I like to insert my expected output into a table.insert into @table1-- expected outputAnd I think I need a dynamic pivot here because id 2 can have more/less name-value combination than id 1.Thanks. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-04 : 20:11:59
|
I'm not sure that'll work because you cannot have more than one column in a set that has the same name. Obviously you can select columns and alias them to the same name, but it doesn't really make much sense. So, in this case, is there any chance you can fix your data model?At any rate, here is how to do a pivot, but it probably doesn't really help in this case:SELECT *FROM @tablePIVOT( MIN(Value) FOR Name IN ( [brand] ,[category] ,[carrier] --,[carrier] ,[color] --,[carrier] ,[model] ,[screen] ,[processor] --,[color] )) AS PivotTable |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 23:04:52
|
other way if you cant change datamodel is to group similar column contents onto a single column and then do pivot over it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-05 : 10:12:52
|
Lamprey/Visakh,Thank you for reply.Lamprey, I understand your point but not sure if I would be able to change the data format because ultimately I want to feed this data into solr.Let's say in solr, I have a schema defined for <carrier>. Now in this case if I combine all carrier into a single column , it will be like this:1 carrier at@t,sprint,verizonBut I don't know if solr will index that combination and yet consider them as different facet values.I will see what can I do. Thank you again guys. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 10:34:14
|
see illustration belowcreate table [tbl](id int,name varchar (100),value varchar (100))insert into tblvalues (1,'brand','apple')insert into tblvalues (1,'category','phone')insert into tblvalues (1,'carrier','at&t')insert into tblvalues (1,'carrier','sprint')insert into tblvalues (1,'color','white')insert into tblvalues (1,'carrier','verizon')insert into tblvalues (1,'model','iPhone5')insert into tblvalues (1,'screen','4')insert into tblvalues (1,'processor','A6')insert into tblvalues (1,'color','black')declare @pivotlist varchar(max)select @pivotlist = stuff((select distinct ',[' + name + ']'from tblfor xml path('')),1,1,'')declare @str varchar(1000)SET @str=';with CTEAS(select t.*,STUFF((SELECT '','' + replace(value,''&'',''|'') from tbl WHERE id = t.id AND name = t.name for xml path('''')),1,1,'''') AS valuelistfrom (select distinct ID, name from tbl)t) SELECT *FROM(SELECT id,name,REPLACE(valuelist,''|'',''&'') AS valuelistFROM CTE)tPIVOT (MAX(valuelist) FOR name IN (' + @pivotlist + '))p'EXEC(@str)output---------------------------------------------------------------------------------------------------id brand carrier category color model processor screen---------------------------------------------------------------------------------------------------1 apple at&t,sprint,verizon phone white,black iPhone5 A6 4 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-05 : 15:42:28
|
Visakh,Thank you. It works great per my need. Is there a way to write same thing without pivot (sql 2000 way) also? I need same thing for one other server also which is running sql 2000.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-06 : 12:01:38
|
I will try and let you know Visakh. Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-06 : 22:46:30
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|