Solved

sql server db select query

Posted on 2013-05-30
11
388 Views
Last Modified: 2013-06-03
Hi

I how to fetch the data in sql server database like below

Ex:
I have a table t1 with two columns item and desc like below

item desc
---- ----
1    red
1    small
1    think
2    white
2    large


I need to fetch the data as below

item desc1 desc2 desc3 desc4
1    red   small think  null
2    white large null  null

I am using sql server database.

Please provide both the querys for sql server databse 2005 and 2008 versions if queries need to write differently in these versions.

Thanks.
0
Comment
Question by:GouthamAnand
  • 6
  • 4
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
does this need to be dynamic? i.e. will the filtering change?
0
 
LVL 4

Expert Comment

by:Ahmed Hussein
Comment Utility
SELECT item ,GROUP_CONCAT(type_count) FROM
(SELECT item,desc,CONCAT(desc,",",COUNT(*)) as type_count
FROM TABLE_NAME GROUP BY item,desc) as x  
GROUP BY item;
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
see this working at: http://sqlfiddle.com/#!3/f26c7/1
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[desc]) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item, ' + @cols + ' from 
            (
                select item, [desc]
                from table1
           ) x
            pivot 
            (
                 max([desc])
                for [desc] in (' + @cols + ')
            ) p '

--select @query
execute(@query)

Open in new window

it uses the PIVOT command
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
if you have trouble using this in sql 2005 I have another approach that will work in both using case expressions and group by
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
think group_concat is for MySQL (only)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:GouthamAnand
Comment Utility
Thank you for your responses.
But can you please suggest the case of static.   Means we assume that thre will be not more than 4 descriptions. ie 4 or less values only for any item.

Thanks.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Also, request you to provide a select query only as I need to join other tables and columns also for my original query. This requirement is part of my actual required query.
0
 

Author Comment

by:GouthamAnand
Comment Utility
Group_concat is not working in sql server database.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
just re-looked at your expected results, this one produces the following:
ITEM   DESC1  DESC2    DESC3
1      red    small    think
2      large  white    (null)

see: http://sqlfiddle.com/#!3/2cd6f/8
DECLARE @query  AS NVARCHAR(MAX)

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN [desc] ELSE null END) AS Desc'
            + convert(varchar,number)
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(c)
                                    FROM (
                                          SELECT
                                                item
                                              , count(*) AS c
                                          FROM table1
                                          WHERE 1=1  /* i.e. filter the data here */
                                          GROUP BY
                                                item
                                        ) AS x
                                    )
           )


select @query = 

  'WITH CTE as ('
+ '        select'
+ '            table1.item, table1.[desc]'
+ '          , row_number() over (partition by table1.Item order by table1.[desc]) as row_ref'
+ '        from table1'
+ '        where 1=1 /* i.e. filter the data here */'
+ '       )'
+ 'select Item, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from CTE group by Item'

--select @query -- uncomment to inspect what the variable contains
exec(@query)
;

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
>>But can you please suggest the case of static.  
apologies, I missed this request. The following is static and assumes at most 4 columns
WITH CTE
AS (
	SELECT
		  table1.item
		-- , table2.whatever -- add fields as needed
		, table1.[desc]
		, row_number() OVER (
			PARTITION BY table1.Item ORDER BY table1.[desc]
			) AS row_ref
	FROM table1
	-- INNER JOIN table2 on table1.id = table2.fk
	-- WHERE 1 = 1 /* i.e. if you need to filter the data, do it here */
	)
SELECT
	  Item
        --, whatever
	, max(CASE WHEN row_ref = 1 THEN [desc] ELSE NULL END) AS Desc1
	, max(CASE WHEN row_ref = 2 THEN [desc] ELSE NULL END) AS Desc2
	, max(CASE WHEN row_ref = 3 THEN [desc] ELSE NULL END) AS Desc3
	, max(CASE WHEN row_ref = 4 THEN [desc] ELSE NULL END) AS Desc4
FROM CTE
GROUP BY
   Item
--, whatever

Open in new window

http://sqlfiddle.com/#!3/2cd6f/10

If you are adding further tables and fields, you can gather the base data within the CTE just like a normal select. Once you have that done then you also need to amend the selection list and group by list of the lower query.

If you are having issues with this please let us know.

Also, just another thing, in your sample data you use the field name [desc] "DESC" is used in SQL to direct an order by so if the field name [desc] is really what you use please ensure it is surround by the square brackets.

and yes, group_concat() is MySQL only - it does not work in sql server. (I did mention this.)
0
 

Author Closing Comment

by:GouthamAnand
Comment Utility
Thank you very much. I change the script accordingly to my requirement and  it works perfectly for my requirement.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now