| Author |
 Topic  |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
 Posted - 02/04/2013 : 15:14:23
|
Hello Gurus,
I need help with following query.
-- table structure with sample data
declare @table table (
id int,
name varchar (100),
value varchar (100)
)
insert into @table
values (1,'brand','apple')
insert into @table
values (1,'category','phone')
insert into @table
values (1,'carrier','at&t')
insert into @table
values (1,'carrier','sprint')
insert into @table
values (1,'color','white')
insert into @table
values (1,'carrier','verizon')
insert into @table
values (1,'model','iPhone5')
insert into @table
values (1,'screen','4')
insert into @table
values (1,'processor','A6')
insert into @table
values (1,'color','black')
select * from @table
-- current output
id name value
1 brand apple
1 category phone
1 carrier at&t
1 carrier sprint
1 color white
1 carrier verizon
1 model iPhone5
1 screen 4
1 processor A6
1 color black
-- expected output
id brand category carrier carrier color carrier model screen processor color
1 apple phone at&t sprint white verizon iPhone5 4 A6 black
Thanks. |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/04/2013 : 15:42:11
|
Ultimately I like to insert my expected output into a table.
insert into @table1
-- expected output
And I think I need a dynamic pivot here because id 2 can have more/less name-value combination than id 1.
Thanks. |
Edited by - ssunny on 02/04/2013 17:32:00 |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 02/04/2013 : 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 @table
PIVOT
(
MIN(Value)
FOR Name IN
(
[brand]
,[category]
,[carrier]
--,[carrier]
,[color]
--,[carrier]
,[model]
,[screen]
,[processor]
--,[color]
)
) AS PivotTable |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47054 Posts |
Posted - 02/04/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/05/2013 : 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,verizon
But 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
India
47054 Posts |
Posted - 02/05/2013 : 10:34:14
|
see illustration below
create table [tbl](
id int,
name varchar (100),
value varchar (100)
)
insert into tbl
values (1,'brand','apple')
insert into tbl
values (1,'category','phone')
insert into tbl
values (1,'carrier','at&t')
insert into tbl
values (1,'carrier','sprint')
insert into tbl
values (1,'color','white')
insert into tbl
values (1,'carrier','verizon')
insert into tbl
values (1,'model','iPhone5')
insert into tbl
values (1,'screen','4')
insert into tbl
values (1,'processor','A6')
insert into tbl
values (1,'color','black')
declare @pivotlist varchar(max)
select @pivotlist =
stuff((select distinct ',[' + name + ']'
from tbl
for xml path('')),1,1,'')
declare @str varchar(1000)
SET @str='
;with CTE
AS
(
select t.*,
STUFF((SELECT '','' + replace(value,''&'',''|'') from tbl WHERE id = t.id AND name = t.name for xml path('''')),1,1,'''') AS valuelist
from (select distinct ID, name from tbl)t
)
SELECT *
FROM
(
SELECT id,name,REPLACE(valuelist,''|'',''&'') AS valuelist
FROM CTE
)t
PIVOT (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 MVP
http://visakhm.blogspot.com/
|
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/05/2013 : 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
India
47054 Posts |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/06/2013 : 12:01:38
|
| I will try and let you know Visakh. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47054 Posts |
Posted - 02/06/2013 : 22:46:30
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Query Help
